One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality. Below is an example of creating both a clustered and non-clustered index on a temp table.
CREATE TABLE #Users ( ID int IDENTITY(1,1), UserID int, UserName varchar(50) ) INSERT INTO #Users ( UserID, UserName ) SELECT UserID = u.UserID ,UserName = u.UserName FROM dbo.Users u CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID) CREATE INDEX IDX_Users_UserName ON #Users(UserName)
Even though you can implicitly create a clustered index on a table variable (@table) by defining a primary key or unique constraint, it is generally more efficient to use a temp table. The downside is recompilation, however this can be suppressed with the use of (Keep Plan) or (Keep Fixed Plan) hints.
