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
DECLARE @EngineerCost MONEY
DECLARE @PartsCost 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.

DECLARE CustomerCursor CURSOR FOR
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

WHILE @@FETCH_STATUS = 0
BEGIN

Populating the Contract Value and Costs

Inside the loop we need to retrieve the monetary values for the contract value, engineer cost and parts cost. Each of these will be held in a variable that will be used in a subsequent UPDATE statement to apply the values to the appropriate columns in the temporary table. Note that the UPDATE statement checks to see if any of the values is null. If any are, they are replaced with zeroes.

SELECT @TotalContractValue = sum(ContractValue) FROM Contracts C
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

SELECT @EngineerCost = sum(J.Duration * E. HourlyRate) FROM Jobs J
INNER JOIN Engineers E ON J.EngineerId = E.EngineerId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

SELECT @PartsCost = sum(TotalCost) FROM UsedParts P
INNER JOIN Jobs J ON P.JobId = J.JobId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber

UPDATE
    #CustomerValue
SET
    TotalContractValue = isnull(@TotalContractValue, 0),
    EngineerCost = isnull(@EngineerCost, 0),
    PartsCost = isnull(@PartsCost, 0)
WHERE
    CustomerNumber = @CustomerNumber

We can now end the loop and close and deallocate the cursor:

    FETCH NEXT FROM CustomerCursor
    INTO @CustomerNumber
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

Calculating the Profits

The cursor completed all of the column data except the Profit column. This can be calculated from the costs and the contract value after the loop has concluded. Once calculated, a query is executed to return the data from the temporary table:

UPDATE #CustomerValue
SET Profit = TotalContractValue - EngineerCost - PartsCost

SELECT * FROM #CustomerValue ORDER BY CustomerNumber

Dropping the Temporary Table

Although a temporary table will be dropped automatically once the connection using it is closed, it is advisable to remove it manually when it is no longer required. As our stored procedure has completed processing the data, the table may be dropped using the same syntax as for a standard table:

DROP TABLE #CustomerValue

This completes the stored procedure. To commit it to the database, execute the script. You can now run the procedure to see the information that it collects and returns.

Creating a Global Temporary Table

The stored procedure made use of a local temporary table, which is visible only to the connect that created it. As mentioned earlier, you can create a temporary table that is visible to all users of a database. To do so, the table name must be prefixed with two hash (or pound) characters.

Try creating the following temporary table and accessing it via two separate connections to the database:

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