Temporary tables are always a great help for a
developer. In the early days when I used Access I used to create
tables that I treated as temporary and then delete them whenever I finished my
task. Using SQL Server
this is much simpler. Or is it?
Types of Temporary tables
There are two types of temporary data types. They
are namely Local and global. Let me first give you and example to start the
temporary table. Following example is taken from Books on Line of Microsoft® SQL
"The two types of temporary tables, local and
global, differ from each other in their names, their visibility, and their
availability. Local temporary tables have a single number sign (#) as the first
character of their names; they are visible only to the current connection for
the user; and they are deleted when the user disconnects from instances of
Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##)
as the first characters of their names; they are visible to any user after they
are created; and they are deleted when all users referencing the table
disconnect from SQL Server" 1
"For example, if you create a table named
employees, the table can be used by any person who has the security
permissions in the database to use it, until the table is deleted. If you create
a local temporary table named #employees, you are the only person who can
work with the table, and it is deleted when you disconnect. If you create a
global temporary table named ##employees, any user in the database can
work with this table. If no other user works with this table after you create
it, the table is deleted when you disconnect. If another user works with the
table after you create it, SQL Server deletes it when both of you disconnect."2
Note: 1,2 above is taken from
the book on line sub heading Creating and Modifying
Unlike in the access days, you do not have to delete
these temporary tables manually, instead you can rely SQL Server to do it
Use of Temporary Tables
Temporary tables are used in several ways. Most
commonly uses to keep the result of a called stored procedure, to reduce the
number of rows for joins, to aggregate data from different sources, or to
replaces cursors and for parameterized views. SQL Server cursors have huge
overhead. Maintenance of code is much easier if you use temporary tables to the
T-SQL. It will be much easier to debug your stored procedure when your using
temporary tables as the data will be saved in temporary tables.
Alternatives to Temporary Tables
There are few alternatives to temporary
tables. Using a derived table is one them. In SQL Server 2000, new data type
called "table" offers same properties as temporary tables. Its main purpose is
for the temporary storage of a set of rows. "table" act as a local variable.
"table" is created in memory unlike the temporary table which will create in
tempdb, which is obviously much faster. Another fact is that "table" uses
limited resources than that of temporary tables.
Limitations of Temporary Tables
Temporary tables are created in the
tempdb database and create additional overhead for SQL Server, reducing overall
performances. SQL Server has numerous problems with operations against temporary
Using Temporary Tables Effectively
If you do not have any option other than to use
temporary tables, use them affectively. There are few steps to be taken.
- Only include the necessary columns and rows rather than using all the columns
and all the data which will not make sense of using temporary tables. Always
filter your data into the temporary tables.
- When creating temporary tables, do not use SELECT INTO statements, Instead of
SELECT INTO statements, create the table using DDL statement and use INSERT INTO
to populate the temporary table.
- Use indexes on temporary tables. Earlier days, I always forget to use a index on
temporary. Specially, for large temporary tables consider using clustered and
non-clustered indexes on temporary tables.
- After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection
is ended. but do not wait until such time.
- When creating a temporary table do not create them with a transaction. If you
create it with a transaction, it will lock some system tables (syscolumns,
sysindexes, syscomments). This will prevent others from executing the same
Generally, temporary tables should be avoided as
much as possible. If you need to use them follow the steps above so that you
have the minimum impact on server performance.