Using Primary Key on Table Variable to improve seek performance

Question

I have this table variable I use in my SP:

DECLARE @t TABLE(ID uniqueidentifier)

Then I insert some data into it (I later use):

INSERT INTO @t(ID)
SELECT ID FROM Categories WHERE ...

And later I have a few SELECT and UPDATE based on @t IDs e.g.:

SELECT * FROM Categories A INNER JOIN @t T ON A.ID = T.ID

etc..

Should I declare ID uniqueidentifier PRIMARY KEY to increase permanence in the SELECT / UPDATE statements? If yes should it be clustered or non clustered? What is the advised option in my case?

EDIT: All my tables in the DB have uniqueidentifier (ID) column as a primary key NONCLUSTERED

EDIT2 : Strangely (or not) when I tried to use PRIMARY KEY NONCLUSTERED on the table variable, when using joined SELECT I see in the execution plan that there is a Table Scan on @t. but when I omit NONCLUSTERED there is a Clustered Index Scan.

Answer 1

If you are worried about performance, you should probably not be using a table variable, but use a temporary table instead. The problem with table variables is that statements referencing it are compiled when the table is empty and therefore the query optimiser always assumes there is only one row. This can result in suboptimal performance when the table variable is populated with many more rows.

Regarding the primary key, there are downsides to make the primary clustered as it will result in the table being physically ordered by the index. The overhead of this sorting operation may outweigh the performance benefit when querying the data. In general it is better to add a non-clustered index, however, as always, it will depend on your particular problem and you will have to test the different implementations.