BlackWaspTM

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 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.

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')
6 February 2010