Temporary tables are a great T-SQL developer feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. However, using temporary tables can adversely affect system performance. Here's a collection of tips to help you manage the temporary tables your T-SQL procedures use. See also, "Using Fake Temporary Tables" and "Table Variables vs. Temporary Tables."
7. Minimize Temporary Table Use
Creating and maintaining temporary tables requires overhead. SQL Server locks the tempdb database while it creates temporary tables and this locking can affect other processes on your system. You can't always avoid using temporary tables, but in some cases you can instead use derived tables, table-type variables, or T-SQL unions.
6. Create Temporary Tables in Procedure
Create your temporary table in the same T-SQL batch that refers to it to avoid batch recompilation. Avoid creating temporary tables in called procedures or by using the EXECUTE statement or sp_executesql procedure.
5. Use Indexes For Large Temporary Tables
As with standard database tables, you can create large temporary tables. Creating indexes for large temporary tables can give your applications a big performance boost.
4. Group DDL in Batches
If you're using temporary tables in a single batch, keep all the data definition language (DDL) statements for a temporary table together. Grouping the temporary table DDL statements together lets SQL Server's query optimizer perform just one batch recompilation when it processes the temporary table statements rather than every time it encounters a DDL operation.
3. Global and Local Temporary Tables
SQL Server supports two types of temporary tables: local temporary tables that only you can access (that begin with one # sign under the standard naming convention) and global temporary tables that multiple users can access (that begin with two # signs). When you use global temporary tables, remember that the global temporary table isn't necessarily deleted when the T-SQL batch that created it ends but when the last T-SQL batch that accesses the temporary table ends.
2. Explicitly Drop Temporary Tables
The system will delete temporary tables when the batch ends. However, explicitly deleting temporary tables will ensure that they're gone as soon as they're no longer needed.
1. Temporary Tables' System Limitations
All statements that use a temporary table must appear after the temporary table is created in the batch. In addition, the T-SQL batch can't use DECLARE CURSOR statements when the SELECT statement references a temporary table.