The question asks only about uniqueness, but a clustered index (created due to a PRIMARY KEY
constraint) also does something else that can be quite beneficial, which seems worth mentioning in order to make these answers widely applicable.
A clustered index imposes an order on its keys. So even if the table variable is only read from, the query optimizer may decide to preserve that order if it allows SQL Server to later skip work it might otherwise need to do. Consider this simple scenario, for instance:
DECLARE @Driver TABLE (id int NOT NULL, PRIMARY KEY CLUSTERED (id DESC))
INSERT INTO @Driver VALUES
(1), (2), (3)
DECLARE @DataTable TABLE (id int NOT NULL PRIMARY KEY, data varchar(50))
INSERT INTO @DataTable VALUES
(1, 'partridge in a pear tree'),
(2, 'turtle doves'),
(3, 'french hens'),
(4, 'calling birds'),
(5, 'golden rings')
SELECT dt.*
FROM @Driver AS d
JOIN @DataTable AS dt ON dt.id = d.id
ORDER BY dt.id DESC
In this case, SQL Server is able to use the order of the clustered index to get the desired output:
id data ----------- -------------------------------------------------- 3 french hens 2 turtle doves 1 partridge in a pear tree
Without requiring an explicit sort:
|--Nested Loops(Inner Join, OUTER REFERENCES:([d].[id])) |--Clustered Index Scan(OBJECT:(@Driver AS [d]), ORDERED FORWARDBACKWARD) |--Clustered Index Seek(OBJECT:(@DataTable AS [dt]), SEEK:([dt].[id]=@Driver.[id] as [d].[id]) ORDERED FORWARD)
Note that SQL Server is able to take advantage of the clustered-index order despite the fact that it's the opposite of the order requested!
In more complicated scenarios, the order provided by the clustered index could permit the use of a Stream Aggregate operator instead of a Hash Aggregate operator. (The former uses less memory and starts returning data sooner.) Disk I/O might be more efficient if reads are done in a particular order as well. In short, there may be a number of benefits due to ordering, depending on the query.