There are times when retrieving data for complex business requirement requires you to temporarily store one or more results sets for a short period of time. Typically these temporary tables are stored in the scope of the current connection, but they may also need to be available across multiple connections. In this level I will be discussing temporary tables, and showing you examples of how to use these transient tables.

What Are Temporary Tables?

What exactly is a temporary table? It is a table that is created, populated, used, and deleted within the scope of a single business process. Temporary tables, as the name suggests, means they are transient and are deleted when they are no longer needed. Temporary tables are created and stored in the tempdb database, and have two different kinds of scope: Local and Global.

A local temporary table is created and used in a single session. Local temporary tables are only available to the session that created the table. Other sessions can't access a local temporary table. Local temporary table are identified by having a single “#” sign as the first character in their name.

Global temporary tables on the other hand are available to all sessions, as long as the session that created the global temporary table is still active. When a table name starts with two “#” signs it is a global temporary table.

The Life of Temporary Tables

As already stated temporary tables are transient, meaning they come and go. They just don't stay around very long.

Temporary tables (both Local and Global) are created just like any other table with a CREATE TABLE statement. But since they are temporary they will be automatically deleted at some point. These temporary tables will be automatically deleted when the session that created them ends. But you can also manually drop local and global temporary tables with a DROP TABLE command as soon as they are not needed.

Limitations

There are a number of limitations you need to be aware of when you use temporary tables. Here is a partial list of those limitations:

• Temporary tables cannot be partitioned
• Temporary tables cannot reference or be referenced by FOREIGN KEY constraints
• When a session creates temporary table that contains a named constraint inside a transaction, another sessions cannot create a temporary table of the same name until the first session commits the transaction.
• To read more about limitations of global and local tables refer to the Books Online documentation that can be found here: http://technet.microsoft.com/en-us/library/ms174979.aspx

Examples of Using Temporary Tables

In this section I will be sharing a number of different examples to give you a better sense of how to use temporary tables. For some of the examples I will be using data from the AdventureWorks2008R2 database. If you want to follow along and run the examples in this article you can download the AdventureWorks2008R2 database from here: http://msftdbprodsamples.codeplex.com/releases/view/93587

For the first example, in listing 1, I will be creating, populating and selecting data from a temporary table.

SET NOCOUNT ON;
CREATE TABLE #Person (
    BusinessEntityId int,
    Title nvarchar(8),
    FirstName nvarchar(50),
    LastName nvarchar(50));
INSERT INTO #Person (BusinessEntityID, Title, FirstName, LastName) 
    SELECT TOP 10 BusinessEntityID, Title, FirstName, LastName
    FROM AdventureWOrks2008R2.Person.Person;
SELECT * FROM #Person;
DROP TABLE #Person;

Listing 1: Creating, Using, and Dropping a local temporary table

In the example in Listing 1, I first created a local temporary table named #Person. You can tell this is a local temporary table because the name of the table starts with a single number sign character (#). Next I populated the newly created local temporary table using an INSERT statement that selected a few columns from the Person.Person table in the AdventureWorks2008R2 database. I then used a SELECT statement to show the rows that were inserted into my #Person local temporary table. Finally, I dropped my local temporary table. As you can see, creating a local temporary table isn't much different than creating any other table.

For the second example let's look at the code in Listing 2.

SET NOCOUNT ON;
CREATE TABLE ##SalesOrderHeader (
    SalesOrderId int,
    SalesOrderDate DateTime,
    CustomerId int);
INSERT INTO ##SalesOrderHeader (SalesOrderId, SalesOrderDate, CustomerId)
    SELECT SOH.SalesOrderID, SOH.OrderDate, C.CustomerID
    FROM AdventureWorks2008R2.Sales.SalesOrderHeader SOH
    LEFT OUTER JOIN AdventureWorks2008R2.Sales.Customer C
    ON  SOH.CustomerID = C.CustomerID;
SELECT TOP 10 * FROM ##SalesOrderHeader;
DROP TABLE ##SalesOrderHeader;

Listing 2: Creating, Using, and Dropping a global temporary table

If you review the code in Listing2 you can see I created, used and dropped the global temporary table named ##SalesOrderHeader just like I did the local temporary table in Listing1. The only different is that table created in Listing 2 starts with two number sign characters (##), instead of one. The double # signs on the ##SalesOrderHeader table name tells SQL Server that this is a global temporary table.

The code in Listing 1 and Listing 2 do not demonstrate the scoping difference of these two kinds of temporary tables. As we already stated the scope of the local temporary table in Listing 1 is the session and the scope of the global temporary table in Listing 2 is all sessions. Let me go through an example that will help show the scoping differences of these two types of temporary tables.

The test I am going to run will create three different sessions. Each session will be running a different series of TSQL commands. I will be using the query window within SQL Server Management Studio for each one of these sessions.

To start this test off I open up three different query windows in SQL Server Management Studio. I then note the Session ID in each window. One way I can do that is to run the command in Listing 3 in each query window.

SELECT @@SPID;

Listing 3: Identifying the Session ID of current session

The next step of my test is to create a local temporary table in one query window, and a global temporary table in another query window. In the query window with the lowest session id number returned when I run the code in Listing 3, I run the code in Listing 4.

CREATE TABLE #Local (TableType varchar(10));
INSERT INTO #Local values ('Local');

Listing 4: Creating and populating a local temporary table

The code in Listing 4 creates a local temporary table.

For the next step I run the code in Listing 5 in the query window that has the second lowest session id number.

CREATE TABLE ##Global (TableType varchar(10));
INSERT INTO ##Global values ('Global');

Listing 5: Creating and populating a global temporary table

The code in Listing 5 creates a global temporary table.

For the last step of my test I run the code in Listing 6 In the query window that has the highest session id.

SET NOCOUNT ON;
SELECT * FROM ##Global;
GO
SELECT * FROM #Local;

Listing 6: Select data from global and local temporary tables

The code in Listing 6 reference both the local and global tables I created in my first two sessions.

When I run the code in Listing 6 and displaying my output in text mode I get the output in Report 1.

TableType
----------
Global
Msg 208, Level 16, State 0, Line 3
Invalid object name '#Local'.

Report 1: Output when code in Listing 6 is run

By reviewing the output in Report 1 you can see I was able to reference the global temporary table in this session, but not the local temporary table . This demonstrates how local temporary tables created in one session are out of scope in another session, and how global temporary table are available in sessions that didn't create the global temporary table. Additionally if you close the first two sessions and then try to run the code in Listing 6, you will see that the global table is no longer available.

Another situation where local temporary are out of scope is when you have nested stored procedure calls. If you read the Books Online documentation it says “temporary tables are only visible in the current session”. In reality temporary tables are only visible in the current session when the nesting level is the same or greater than the nest level in which the temporary table was created. Let me demonstrate this by running the code in Listing 7.

SET NOCOUNT ON;
GO
CREATE PROC MyProc 
AS
CREATE TABLE #Level1 (Level int);
INSERT #Level1 VALUES (1);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
SELECT * FROM #Level0;
SELECT * FROM #Level1;
GO
CREATE TABLE #Level0 (Level int);
INSERT #Level0 VALUES (0);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
EXEC MyProc;
SELECT * FROM #Level0;
SELECT * FROM #Level1;

Listing 7: Temporary tables in same session, but different nest level

When I run this code I get the output in Report 2.

Level       SPID
----------- ------
1           55
Level
-----------
0
Level
-----------
1
Level       SPID
----------- ------
0           55
Level
-----------
0
Msg 208, Level 16, State 0, Line 6
Invalid object name '#Level1'.

Report 2: Output when running Listing 7

By reviewing the code and output associated with Listing 7 we can see that when a local temporary table is created at higher nesting level it is not available to a lower nesting level, even when they are created in the same session. In Listing 7, I created and populated a temporary table named #Level0 in nesting level 0, then I called a stored procedure MyProc that runs in nesting Level 1. In that stored procedure I create and populated a temporary table named #Level1. In that same stored procedure I displayed the nest level and SPID (Session ID), and selected data from the two local temporary tables (#Level0 and #Level1) that were created by my session. I was successful in querying both local temporary tables in MyProc because the #Level0 table was created at a lower nest level, and #Level1 table was created at the same nesting level. But when my stored procedure MyProc ends SQL Server automatically deleted the temporary table #Level1. This caused the last SELECT statement to fail with an “Invalid object name” error because I was trying to reference the temporary table #Level1 that was created in higher nest level then my current nest level.

Best Practices

Here are few best practices to consider when creating temporary tables:

• Before you create a temporary table, verify you can't create a set based query that does not require the creation of a temporary table.
• Explicitly drop temporary tables as soon as they are no longer needed.

Summary

Local temporary tables are a great way to store a result set for a short period of time so you can use it in additional processing within the same session. But keep in mind temporary tables take space in tempdb and therefore you should drop them as soon as they are not needed any longer. If you need your temporary tables to be available by multiple sessions then you will need to create a global temporary table.

Question and Answer

In this section you can review how well you have understood using local and global temporary tables by answering the following questions.

Question 1:

What characteristic is not associated with local temporary tables?

• They have to be explicitly deleted prior to the session ending
• They are created in tempdb
• They can be used to store temporary data for additional processing in the same session
• They are created just like normal tables using the CREATE TABLE statement but the table name starts with a single # sign.

Question 2:

Which two statements best describe the differences between a local and a global temporary table (Pick two)?

• Local temporary tables are available to only the current session whereas global temporary tables are available to all sessions.
• Local temporary tables are available to all sessions whereas global temporary tables are only available to the current session.
• Local temporary table names start with a single # sign, and global temporary tables start with two # signs.
• Local temporary table names start with two # signs, and global temporary tables start with a single # sign.

Question 3:

What happens to local and global temporary tables when the session in which they were created ends?

• The temporary tables stay around until SQL Server is recycled.
• The temporary tables are automatically dropped.
• The temporary tables are flagged as unavailable and removed at some future point in time when SQL Server is idle.
• The sessions that created the temporary tables fail, because temporary tables must be dropped prior to a session ending.

Answers:

Question 1:

The answer is a. Local temporary tables do not have to be explicitly deleted prior the session ending. They can be deleted prior to the session ending, but it is not required. If a local temporary table is not deleted prior to the session ending it will be deleted automatically when the session ends.

Question 2:

The correct answer is a and c. b is incorrect because local temporary tables are only available to the current session and global temporary tables are available to all session. d is incorrect because local temporary tables start with a single number sign (#) and global temporary tables start with two number signs (##).

Question 3:

The correct answer is b. Global and temporary tables are dropped automatically when the sessions that created them ends. However, best practice is to manually drop temporary tables as soon as they are no longer needed to free up space in the tempdb database.