When you create your temp table you can define primary keys and indexes as well. This may improve performance.
Here's a copy of some notes I put together for my developers concerning the use of temp tables (additions and/or corrections are welcomed):
Always include the following before creating the temp table and then again as soon as the temp table is no longer needed. The temp table will go out of scope by itself when the procedure ends, but if an error occurs when testing before the table goes out of scope it may still exist and need to be manually dropped.
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
Then when creating the table and inserting the data…
“HEAP” METHOD (no primary key or index). EASY to code and could be used for SMALL temp tables with just a few rows. It shouldn’t be used if the table is ever part of a JOIN since it has no primary key or index. Note that using an ORDER BY on an insert adds unnecessary overhead since SQL chooses the insert order internally and ignores the Order By even though the ordering is executed by the select statement.
SELECT ProductID, CategoryID
INTO #TempTable
FROM dbo.Products
ORDER BY CategoryID -- Note that a sort here is unnecessary overhead
“BAD” CLUSTERED INDEX METHOD (manual creation of a named primary key). A MAJOR potential problem with this method is that since the Primary Key is specifically named, if the stored procedure is called concurrently by another process the constraint creation will fail since it already exists in the scope of a different procedure. To avoid this issue the PK name would have to be dynamically created and inserted into a string of dynamic sql text and executed that way (see the very last section where this is done for a non-clustered index). There is an easier way to do the same thing (next section)!
CREATE TABLE #TempTable'
(
[ID] INT IDENTITY(1,1) NOT NULL,
[ProductID] INT NULL,
[CategoryID] INT NULL,
CONSTRAINT [PK_#TempTable'] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #TempTable
SELECT ProductID, CategoryID
FROM dbo.Products
“GOOD” CLUSTERED INDEX METHOD (auto creation of primary key). This method creates a unique Primary Key (in this example the form [PK__#TempTable__3214EC27582F7143]). The code is cleaner and there is no chance of collisions. If desired, the PK can contain multiple columns just like any index or key like “PRIMARY KEY (ID,ProductID,CategoryID)”. This would be the MOST efficient method since all the columns would be indexed, but of course this could only work if all the columns are [NOT NULL] and the combined values of the columns are always unique.
Usually it’s not worth the trouble, but if an additional non-clustered index is desired then it should be created AFTER any data is inserted and it must have a unique name to avoid collisions with concurrent runs of the procedure. The reason the non-clustered index should be created after the data insert is because without data there will have to be a single update to the index for every row inserted. By inserting the data first, there only has to be a one-time creation of the non-clustered index—which in net is much faster than individual updates. This is not an issue for a primary key (clustered unique index) because the records are inserted in order and no key lookups or key inserts are required when inserting an initial batch of new records into an empty table.
CREATE TABLE #TempTable
(
[ID] INT IDENTITY(1,1) NOT NULL,
[ProductID] INT NULL,
[CategoryID] INT NULL,
PRIMARY KEY (ID),
UNIQUE (ID))
INSERT INTO #TempTable
SELECT ProductID, CategoryID
FROM dbo.Products
/* Optional if additional non-clustered index(s) are desired. */
/* Note: string values must be NVARCHAR */
DECLARE
@strCreateIndex NVARCHAR(1000)
,@IndexUniqueID NVARCHAR(50)
SET @IndexUniqueID = N'IX__#TempTable__'
+ REPLACE(CAST(NEWID() AS NVARCHAR(50)),'-','')
SET @strCreateIndex = N'CREATE NONCLUSTERED INDEX ['
+ @IndexUniqueID + '] ON [#TempTable] ([CategoryID],[ProductID])'
EXEC sp_executesql @strCreateIndex
BTW, Microsoft (since SQL2005) recommends using actual temp tables (#temp) rather than table variables (@temp). The primary reason is that using a #temp table allows normal procedure compilation and caching, whereas table variables (@temp) may force a recompile every time a procedure is run. Also, table variables do not update statistics or the transaction log so they will fail if used in a TRANS/ROLLBACK TRANS scenario. There is a myth that table variables reside in memory and thus improve performance (one of the articles below makes that mistaken assumption). But other articles I’ve read have proved that even temp variables use tempdb and performance tests between the two temp table types show no difference. There are a BUNCH of other reasons listed for not using table variables here:
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
and
HOWEVER, in spite of all this…only table variables (@temp) can be used inside of functions if a temp table is required. So that is one (perhaps the only) legitimate use of the table variable (@temp) method.