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:
CREATE PROCEDURE GetProfitPerCustomer AS
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
27 January 2010