Primary Key in SQL Server Table Variable
-
Is there any benefit in setting a primary key in a table variable? Especially if you will perform SELECT and INSERT operations on it? Like,
DECLARE @Tbl TABLE(Id INT PRIMARY KEY, Name NVARCHAR(36)) -
Is there any benefit in setting a primary key in a table variable? Especially if you will perform SELECT and INSERT operations on it? Like,
DECLARE @Tbl TABLE(Id INT PRIMARY KEY, Name NVARCHAR(36))John Gathogo wrote:
Is there any benefit in setting a primary key in a table variable?
Yes, mainly to ensure referential integrity. That's probably an unsatisfying answer. Imagine that your table only has "name" defined in it. We could add "John" as a record, and have a list of things that John should do in some other table. Might look like this;
TABLE Users (Name NVARCHAR(36))
"John"
"Peter"TABLE Tasks (Name NVARCHAR(36), ThingToDo NVARCHAR(50))
"John", "Water the plants"
"John", "Cook dinner"
"Peter", "Write an article for CodeProject"We're in trouble as soon as someone adds another user with the firstname "John"; we would no longer be able to differentiate between the tasks of the first "John" and the second "John". Now, it's a "best practice" to define a set of column-names that make a record unique. We could add the [Birthdate] of the user to the "Users" table, that way we could distingiush between the first and the second Johnny. It's easier[^] to just add a generated identity and to use that as a primary key. A primary key is often accompanied by a clustered index. There's a heap of information on MSDN[^], if that is your kind of thing :)
I are Troll :suss: