Create Index on Table Variable

Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index.

[cc lang=”sql”]
DECLARE @Users TABLE
(
UserID INT PRIMARY KEY,
UserName varchar(50),
UNIQUE (UserName)
)
[/cc]

The drawback is that the indexes (or constraints) need to be unique. One potential way to circumvent this however, is to create a composite unique constraint:
[cc lang=”sql”]
DECLARE @Users TABLE
(
UserID INT PRIMARY KEY,
UserName varchar(50),
FirstName varchar(50),
UNIQUE (UserName,UserID)
)
[/cc]

You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word.
[cc lang=”sql”]
DECLARE @Users TABLE
(
UserID INT PRIMARY KEY,
UserName varchar(50),
FirstName varchar(50),
UNIQUE CLUSTERED (UserName,UserID)
)
[/cc]

Generally, temp tables perform better in situations where an index is needed. The downfall to temp tables is that they will frequently cause recompilation. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. Contrary to popular belief, table variables can and do write to disk.