Indexes on Table Variables

It is a common misunderstanding when comparing temporary tables and table variables that one difference is that table variables cannot have indexes. While table variables are not directly materialized and you cannot execute CREATE INDEX on them, an index can be part of the definition of the table variable.

Here is one example with two indexes on a table variable.

SET SHOWPLAN_TEXT ON;
GO
 
DECLARE @Employees TABLE (emp_nbr INT PRIMARY KEY NONCLUSTERED,
                          emp_name VARCHAR(35),
                          ssn CHAR(11) UNIQUE CLUSTERED);
 
 
INSERT INTO @Employees VALUES(1, 'Jim Brown', '987-11-0234');
INSERT INTO @Employees VALUES(2, 'Chris Defoe', '987-13-9899');
INSERT INTO @Employees VALUES(3, 'Keith Goldwin', '986-31-1543');
 
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE emp_nbr = 2;
 
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE ssn LIKE '998-13%';
 
--Results
  |--Nested Loops(Inner Join, OUTER REFERENCES:([ssn]))
       |--Index Seek(OBJECT:(@Employees), SEEK:([emp_nbr]=(2)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn]=[ssn]) LOOKUP ORDERED FORWARD)
 
  |--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn] >= '998-12þ' AND [ssn] < '998-14'), WHERE:([ssn] like '998-13%') ORDERED FORWARD)