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.
Popular search terms:
Hi Derek,
How about creating index immediately after table declaration? Does it eliminate recompilation?
Nah, you can’t count on that as mitigating recompilation. It actually has to do with how much data was in the table at the time of compilation. If that changes significantly in a subsequent execution, it will recompile. The precise descriptions of the data changes are published somewhere.