This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Temporary Tables

The fifty-seventh part of the SQL Server Programming Fundamentals tutorial investigates temporary tables. Temporary tables can be used to hold information during long-running or complex operations without locking live data rows.

What are Temporary Tables?

Temporary tables are a special type of table that, as the name suggests, are used to hold data temporarily. This can be useful when performing very complex operations that, if executed against live data, could affect performance for other users or could lock rows for an unacceptable length of time. Temporary tables are often created and populated with a copy of the data to be processed. Once the activities that require them are completed, they are dropped.

Temporary tables can be local or global. A local table is visible only to the session that it is created from and is dropped automatically when the connection is closed. Other users cannot access the table, although they may create their own temporary with the same name. In such situations the two temporary tables are in no way linked. Global temporary tables are available to all users and are automatically dropped when the last user accessing the table disconnects from the database. Although both types of table will be automatically dropped, it is common practice to delete them manually when no longer required.

When a temporary table is created, a physical table is created in the tempdb database. This table has a name that is similar to that specified but that is modified slightly to ensure that it is unique. This allows two users to apparently create temporary tables of the same name. When information is added to the table, it is added to the physical table in tempdb and written to disk. The performance of a temporary table is, therefore, similar to any other table.

Using Temporary Tables

The examples in this article use the JoBS tutorial database, which has been created and populated with data throughout the course of these articles. To create an up to date version of the database, download and execute the script using the link at the top of the page.

To demonstrate the use of temporary tables we will add a new stored procedure to the JoBS database. This stored procedure will be used to obtain the contract value for each customer and the total costs of any repair work undertaken. The information will be gathered from the Contracts, Jobs, PartsUsed and Engineers tables. Once compiled, the information will be used to calculate a profit or loss value for every customer. This information could be extracted using a complex query but for demonstration purposes we will use a temporary table and a cursor.

Creating the Stored Procedure

The stored procedure requires no parameters as it retrieves information for all customers with no filtering of data. The declaration is therefore simple:


Some variables are required to temporarily hold the customer number, total contract value, total cost of engineer time and total cost of parts used for each customer. These should be declared immediately after the previous code, as follows:

DECLARE @CustomerNumber INT
DECLARE @TotalContractValue MONEY

Creating the Temporary Table

The next step in the stored procedure is to create the temporary table. The table will hold the contract value, costs and profit for every customer. This information will be populated in further steps before being selected as the procedure's returned data set.

Creating a temporary table uses the same syntax used to create a standard table. To signify that the table is a local temporary table, a hash (or pound) symbol (#) is used as a prefix to the table name. The name may be up to 116 characters in length.

Add the following temporary table creation statement to the stored procedure code:

CREATE TABLE #CustomerValue
    CustomerNumber INT,
    TotalContractValue MONEY,
    EngineerCost MONEY,
    PartsCost MONEY,
    Profit MONEY

Populating the Initial Data

To initialise the data in the temporary table we will copy all of the customer numbers from the live data. All other columns in the will contain null values at this point. We can copy the customer numbers using an INSERT statement that sources its information from a query as follows:

INSERT INTO #CustomerValue (CustomerNumber)
SELECT CustomerNumber FROM Customers

Declaring the Cursor

The next step is to populate the contract value and costs columns. To achieve this we will use a cursor to allow us to process the data one row at a time. The cursor is simple, being based upon selecting the only populated column from the temporary table with no filtering of rows.

Add the following to the stored procedure to declare the cursor.

SELECT CustomerNumber FROM #CustomerValue
OPEN CustomerCursor

To open the cursor and define the start of a WHILE loop, add the following code:

FETCH NEXT FROM CustomerCursor
INTO @CustomerNumber

27 January 2010