SQL Server Table Variables

The fifty-eighth part of the SQL Server Programming Fundamentals tutorial describes table variables. These special variables differ from those that hold single values by allowing a table structure to be temporarily declared and populated with information.

>> Download Script

What are Table Variables?

A table variable provides functionality similar to a standard variable and a local temporary table combined. Standard variables are used to temporarily hold a single value within the scope of a script or stored procedure. Temporary tables are used to define an entire table, stored in the tempdb database, for the period of some process. Table variables are variables that have a specific scope and permit a table-like structure to be constructed and populated with many rows of data.

Table variables are often used where a temporary table would be utilised otherwise. Their primary use is for storing information that is to be returned from user-defined functions. However, it is not uncommon to see table variables used within stored procedures, triggers, etc. In such cases, the variable exists only within the scope of the procedure. When the process terminates, the table variable is automatically destroyed. It cannot be passed into an input parameter of another procedure or returned as an output parameter. If data needs to be returned by a stored procedure, the variable would need to be queried before the procedure ends.

Table variables may be stored partially within memory or in the tempdb database if enough RAM is not available. They use fewer resources for locking and logging than temporary tables and are not included in transactions. This can provide improved performance when compared to a temporary table but does mean that table variables are not affected when a transaction is rolled back. However, as table variables cannot have explicitly declared indexes, do not have parallel execution plans and are excluded from statistics gathering, performance can be lower than the equivalent temporary table. Table variables tend to be better for small sets of data or when the queries executed against them are not complex. Temporary tables often increase the performance for large data sets and complex ad-hoc operations. To determine which to use for a given scenario, you should implement both and measure the performance before selecting an approach.

Using Table Variables

The examples in this article use data from the JoBS database. This is the tutorial database that has been created throughout this series of articles. To obtain an up to date version of the database, download and execute the script using the link at the top of the page.

Declaring a Table Variable

The syntax for declaring a table variable is similar to that of creating a table. The DECLARE statement is used to name the table variable and the columns names and types are included as a comma-separated list within parentheses. The following sample code declares a new table variable with two columns.

DECLARE @TV TABLE
(
    ID INT,
    Name VARCHAR(20)
)

Adding Data to a Table Variable

Once a table variable is declared, information can be inserted, updated, deleted and queried as if the variable where a true table. The following script inserts two rows, updates one of those rows and then executes a query against the table variable. This script must be executed in the same batch as the previous declaration to operate successfully. If the declaration is executed alone, the variable will be out of scope before the data can be manipulated.

INSERT INTO @TV VALUES (1, 'Value 1')
INSERT INTO @TV VALUES (2, 'Value 3')
UPDATE @TV SET Name = 'Value 2' WHERE ID=2
SELECT * FROM @TV

Adding Other Table Features

Table variables can include many table features including primary keys, unique keys, check constraints, identity columns and default values. Each is declared using the same syntax as for a true table. In the following example the table includes a primary key. If you run the script, you will see that the second insert fails because it would require the creation of a duplicate primary key value.

DECLARE @TV TABLE
(
    ID INT PRIMARY KEY,
    Name VARCHAR(20)
)

INSERT INTO @TV VALUES (1, 'Value 1')
INSERT INTO @TV VALUES (1, 'Value 2')

Updating the GetProfitPerCustomer Stored Procedure

In the previous article in this series we created a stored procedure that used a cursor and a temporary table whilst determining the profit generated for each customer in the JoBS database. This stored procedure could have used a table variable instead of the temporary table. To change the stored procedure to use a table variable would require several minor modifications:

The creation of the temporary table would be replaced with the declaration of a table variable, "@CustomerValue".
All references to #CustomerValue would be changed to @CustomerValue.
The commands used to drop the temporary table would no longer be needed.

To modify the stored procedure, you can execute the following script.

ALTER PROCEDURE GetProfitPerCustomer AS

DECLARE @CustomerNumber INT
DECLARE @TotalContractValue MONEY
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY

DECLARE @CustomerValue TABLE
(
    CustomerNumber INT,
    TotalContractValue MONEY,
    EngineerCost MONEY,
    PartsCost MONEY,
    Profit MONEY
)

INSERT INTO @CustomerValue (CustomerNumber)
SELECT CustomerNumber FROM Customers

DECLARE CustomerCursor CURSOR FOR
SELECT CustomerNumber FROM @CustomerValue

OPEN CustomerCursor

FETCH NEXT FROM CustomerCursor
INTO @CustomerNumber

WHILE @@FETCH_STATUS = 0
BEGIN

    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	

    FETCH NEXT FROM CustomerCursor
    INTO @CustomerNumber
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

UPDATE
    @CustomerValue
SET
    Profit = TotalContractValue - EngineerCost - PartsCost

SELECT * FROM @CustomerValue ORDER BY CustomerNumber

Prev << SQL Server Temporary Tables

Next >> SQL Server User-Defined Functions

Link to this Page6 February 2010

Twitter

RSS