Introduction

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

Server™ 2000.

"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" ¹

"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."²

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

automatically.

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

tables.

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

  query.

Conclusion

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.