Table Variables in MS SQL Server

Table variables in SQL Server are variables that store a set of records in a table like structure for a particular period of time. It is another way of storing data temporarily similar to temporary tables. In this article, we will see what is table variable with an example.

Generally, the name of the table variable should start with ‘@’ and followed by a meaningful name. Declaring the table variable just like declaring the local variables in SQL Server.

Table Variable Syntax

DECLARE @TableVariableName TABLE 
(
    column1 datatype, 
    column2 datatype
);

For example, let’s populate the list of Indian players from the table ‘tblPlayer’.

DECLARE @PlayerDetails TABLE 
(playerID INT NOT NULL, playerName VARCHAR(50) NOT NULL);

INSERT INTO @PlayerDetails
SELECT playerID, FirstName
FROM tblPlayer
WHERE countryID = 1;

SELECT * FROM @PlayerDetails;

Queries in the above code snippet, declares the table variable ‘@PlayerDetails’ with two columns, inserts the data into it and retrieves the data from it.

Scope

The scope of the table variable ends as soon as it exits the batch of SQL statements. A function or stored procedure which contains table variable ends the scope once the execution is completed.

Limitations

• Define the structure of the table variable during the declaration. It is not possible to alter the structure of it after the declaration.
• Unlike other data types in SQL server, you cannot use table variable as an input or output parameter.
• It is also important to note that the table variables do not participate in transactions. Although we use rollback command in the below code snippet, the select query returns two records.

DECLARE @PlayerDetails TABLE 
(playerID INT NOT NULL, playerName VARCHAR(50) NOT NULL);

BEGIN TRAN T1
INSERT INTO @PlayerDetails VALUES (1, 'Sachin'), (2, 'Shewag')
ROLLBACK TRAN T1

SELECT * FROM @PlayerDetails;

Difference between table variable and temp table

Table Variable	Temp Table
Declare the table variable with TABLE keyword which stores records.	Create the temp table using CREATE TABLE or SELECT INTO statement with a prefix # or ## as a table name.
It is not possible to alter table variable after declaration.	It is possible to alter temp tables structure after creating it.
Scope of table variable ends after the execution of batch.	Scope of the temp table is based on the type and it ends when the connection is closed.
Table variable can’t be dropped.	Temp table can be dropped automatically or manually.
Table variable can be used in functions.	Temp table can’t be used in functions.
Table variable does not participate in transactions.	Temp table can participate in transactions.