The Table Variable in SQL Server

 

The Table Variable in SQL Server

In this article, we will explore the table variable in SQL Server with various examples and we will also discuss some useful tips about the table variables.

Definition

The table variable is a special type of the local variable that helps to store data temporarily, similar to the temp table in SQL Server. In fact, the table variable provides all the properties of the local variable, but the local variables have some limitations, unlike temp or regular tables.

Syntax

The following syntax describes how to declare a table variable:

DECLARE @LOCAL_TABLEVARIABLE TABLE

(column_1 DATATYPE, 

 column_2 DATATYPE, 

 column_N DATATYPE

)

If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables. The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable. After the TABLE keyword, we have to define column names and datatypes of the table variable in SQL Server.

In the following example, we will declare a table variable and insert the days of the week and their abbreviations to the table variable:

DECLARE @ListOWeekDays TABLE(DyNumber INT,DayAbb VARCHAR(40) , WeekName VARCHAR(40))

 

INSERT INTO @ListOWeekDays

VALUES 

(1,'Mon','Monday')  ,

(2,'Tue','Tuesday') ,

(3,'Wed','Wednesday') ,

(4,'Thu','Thursday'),

(5,'Fri','Friday'),

(6,'Sat','Saturday'),

(7,'Sun','Sunday')

SELECT * FROM @ListOWeekDays

At the same time, we can update and delete the data contained in the table variables. The following query delete and update rows:

DECLARE @ListOWeekDays TABLE(DyNumber INT,DayAbb VARCHAR(40) , WeekName VARCHAR(40))

 

INSERT INTO @ListOWeekDays

VALUES 

(1,'Mon','Monday')  ,

(2,'Tue','Tuesday') ,

(3,'Wed','Wednesday') ,

(4,'Thu','Thursday'),

(5,'Fri','Friday'),

(6,'Sat','Saturday'),

(7,'Sun','Sunday')

DELETE @ListOWeekDays WHERE DyNumber=1

UPDATE @ListOWeekDays SET WeekName='Saturday is holiday'  WHERE DyNumber=6

SELECT * FROM @ListOWeekDays

DML statment usage for table variable in SQL Server

What is the storage location of the table variables?

The answer to this question is – table variables are stored in the tempdb database. Why we underline this is because sometimes the answer to this question is that the table variable is stored in the memory, but this is totally wrong. Before proving the answer to this question, we should clarify one issue about the table variables. The lifecycle of the table variables starts in the declaration point and ends at the end of the batch. As a result, the table variable in SQL Server is automatically dropped at the end of the batch:

DECLARE @ExperiementTable TABLE

( 

TestColumn_1 INT, TestColumn_2 VARCHAR(40), TestColumn_3 VARCHAR(40)

);

SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE

FROM tempdb.INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE 'TestColumn%';    

GO

SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE

FROM tempdb.INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE 'TestColumn%';

Table variables are stored in the tempdb database:

As you can see, the previous query returns two result sets. The ResultSet-1 contains column names and data types of the declared table variable and the ResultSet-2 does not contain any data. The reason for this case is, the first INFORMATION_SCHEMA.COLUMNS view, and table variable executed in the same batch so we can get the information of the @ExperiementTable table variable from the tempdb database. The second query could not return any data about the @ExperiementTable because the GO statement ends the batch so the life-cycle of the @ExperiementTable table variable is terminated. In this section, we proved the storage location of the table variable in SQL Server.