Temporary Tables

Temporary tables should be avoided if possible. The reason for this is that they are created and then dropped in tempdb database. For this happen the SQL process needs to acquire an exclusive lock on the tempdb database each time. If too many temporary tables are used, performance can degrade as the various processes have to wait on each other for access.

A good alternative to temporary tables are table variables. Table variables reside in memory only and are a much more efficient alternative. However, because they reside only in memory, table variables should only be used for small data sets (< 1000 records).

For example,

DECLARE @MyTable TABLE
(
	Id		INT UNIQUE IDENTITY,
	PU_Id	    INT,
	TimeStamp      DATETIME,
	PRIMARY KEY    ( PU_Id, TimeStamp )
);

Single or multiple column clustered indexes can be created by declaring a PRIMARY KEY. Additional, non-clustered indexes can be created using the UNIQUE constraint keyword.

From SQL 2014 (@@version >= 12.x), we can define the Additional Non-Clustered Indexes while declaring the Table Variables.

DECLARE @MyTable TABLE
(
	Id		INT UNIQUE IDENTITY,
	PU_Id      INT,
	TimeStamp  DATETIME,
	PRIMARY KEY ( PU_Id, TimeStamp ),
	INDEX IDX1 NONCLUSTERED (TimeStamp)
);

For large datasets, temporary tables should be used. For good performance, temporary tables should always have a clustered index on them.

One thing to remember about temporary tables is that they are declared globally and are available to any stored procedures called by the stored procedure that created the table. Any duplicate create statements in the called stored procedures do not generate any error messages and the original table is used which, if unintended, generates some unexpected results.

Multiple temporary tables should always be created together at the beginning of a stored procedure to reduce recompiles. Temporary tables are automatically dropped at the end of the stored procedure that created them but they should be explicitly dropped (using the DROP TABLE statement) as soon as they are no longer needed in order to free up system resources.

When using temporary tables and/or table variables it is very important to ensure the table has an index. Lack of proper indexes is a leading cause of poor performance.