SQL Server Table Variable

SQL Server Table variable is a local variable that stores data temporarily, similar to the temporary table in SQL Server.

Tempdb database is used to store table variables.

To declare a table variable in SQL Server, start the DECLARE statement.

The name of the table variable must start with at(@) sign. The TABLE keyword defines that the used variable is a table variable. After the TABLE keyword, define column names and datatypes of the table variable in SQL Server.

SQL Server Table Variable Syntax is:

DECLARE @TABLEVARIABLE TABLE(column1 datatype, column2 datatype, columnN datatype)

Let’s look at some examples of the SQL Server Table Variables, and understand how to declare, update, and delete table variables.

Declare Table Variable in SQL Server Example

In this example, we will declare a Table variable and insert values in it.

Query:

DECLARE @WeekDays TABLE (Number INT, Day VARCHAR(40), Name VARCHAR(40))  

INSERT INTO @WeekDays
VALUES
(1, 'Mon', 'Monday'),
(2, 'Tue', 'Tuesday'),
(3, 'Wed', 'Wednesday'),
(4, 'Thu', 'Thursday'),
(5, 'Fri', 'Friday'),
(6, 'Sat', 'Saturday'),
(7, 'Sun', 'Sunday')

SELECT * FROM @WeekDays;

Output:

Update and Delete Commands for table variable in SQL Server

In this example, we will update and delete the data in the table variables.

Query:

DELETE @WeekDays WHERE Number=7;

UPDATE @WeekDays SET Name='Saturday is a holiday' WHERE Number=6 ;

SELECT * FROM @WeekDays;

• A table variable in SQL Server is a local variable that stores data temporarily, similar to temporary tables.
• It provides all the properties of a local variable but with some limitations compared to temp or regular tables.
• Table variables can be used for operations like insert, update, delete, and select within a SQL script.
• Table variables are not available after the execution of a complete query, unlike temporary tables that persist after the query execution.
• The lifecycle of a table variable starts from its declaration and ends when the declaration batch or stored procedure goes out of scope.
• Table variables can be used in SELECT, INSERT, UPDATE, and DELETE statements within their scope.
• The INTO clause in a SELECT statement cannot be used to create and populate a table variable.
• The structure of a table variable cannot be changed after it has been declared.
• Indexes cannot be created on table variables using the CREATE INDEX statement.