When to use temporary table in SQL Server 2005

Question

I read about temporary tables, global temporary tables and table variables. I understood it but could not imagine a condition when I have to use this. Please elaborate on when I should use the temporary table.

Answer 1

Most common scenario for using temporary tables is from within a stored procedure.

If there is logic inside a stored procedure which involves manipulation of data that cannot be done within a single query, then in such cases, the output of one query / intermediate results can be stored in a temporary table which then participates in further manipulation via joins etc to achieve the final result.

One common scenario in using temporary tables is to store the results of a SELECT INTO statement

The table variable is relatively new (introduced in SQL Server 2005 - as far as i can remember ) can be used instead of the temp table in most cases. Some differences between the two are discussed here

In a lot of cases, especially in OLTP applications, usage of temporary tables within your procedures means that you MAY possibly have business processing logic in your database and might be a consideration for you to re-look your design - especially in case of n tier systems having a separate business layer in their application.

Answer 2

The main difference between the three is a matter of lifetime and scope.

By a global table, I am assuming you mean a standard, run of the mill, table. Tables are used for storing persistent data. They are accessible to all logged in users. Any changes you make are visible to other users and vice versa.

A temporary table exist solely for storing data within a session. The best time to use temporary tables are when you need to store information within SQL server for use over a number of SQL transactions. Like a normal table, you'll create it, interact with it (insert/update/delete) and when you are done, you'll drop it. There are two differences between a table and a temporary table.

1. The temporary table is only visible to you. Even if someone else creates a temporary table with the same name, no one else will be able to see or affect your temporary table.
2. The temporary table exists for as long as you are logged in, unless you explicitly drop it. If you log out or are disconnected SQL Server will automatically clean it up for you. This also means the data is not persistent. If you create a temporary table in one session and log out, it will not be there when you log back in.

A table variable works like any variable within SQL Server. This is used for storing data for use in a single transaction. This is a relatively new feature of TSQL and is generally used for passing data between procedures - like passing an array. There are three differences between a table and a table variable.

1. Like a temporary table, it is only visible to you.
2. Because it is a variable, it can be passed around between stored procedures.
3. The temporary table only exists within the current transaction. Once SQL Server finishes a transaction (with the GO or END TRANSACTION statements) or it goes out of scope, it will be deallocated.

I personally avoid using temporary tables and table variables, for a few reasons. First, the syntax for them is Microsoft specific. If your program is going to interact with more than one RDBMS, don't use them. Also, temporary tables and table variables have a tendency to increase the complexity of some SQL queries. If your code can be accomplished using a simpler method, I'd recommend going with simple.