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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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%'; |
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.
How can we use constraints with the table variables?
Constraints are database objects that ensure data integrity. Table variables allow us to create the following constraints:
- Primary Key
- Unique
- Null
- Check
In the following example, we will successfully use all types of constraints on the table variable seamlessly:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @TestTable TABLE (ID INT PRIMARY KEY, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL, Col3 int CHECK (Col3>=18)) INSERT INTO @TestTable VALUES(1,'Value1',12 , 20) SELECT * FROM @TestTable |
On the other hand, Foreign Key constraints cannot use for the table variables. The other restriction is, we have to define the constraints when we are declaring the table variable otherwise, we experience an error. For example, the following query will return an error because of this restriction. We cannot alter the table structure after the declaration of the table variable:
|
1 2 3 4 5 |
DECLARE @TestTable TABLE (ID INT NOT NULL ) ALTER TABLE @TestTable ADD CONSTRAINT PK_ID PRIMARY KEY (ID) |
Transactions and table variable in SQL Server
Transactions are the smallest logical unit that helps to manage the CRUD (insert, select, update and delete) operations in the SQL Server. Explicit transactions are started with BEGIN TRAN statement and they can be completed with COMMIT or ROLLBACK statements. Now we will execute the following query and then analyze the result:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @TestTable TABLE (ID INT PRIMARY KEY, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL, Col3 int CHECK (Col3>=18)) BEGIN TRAN INSERT INTO @TestTable VALUES(1,'Value1',12 , 20) ROLLBACK TRAN SELECT * FROM @TestTable |
Table variable CRUD operations do not manage by explicit transactions. As a result, ROLLBACK TRAN cannot erase the modified data for the table variables.
Some useful tips for the table variables
TRUNCATE statement does not work for table variables
The TRUNCATE statement helps to delete all rows in the tables very quickly. However, this statement cannot be used for table variables. For example, the following query will return an error:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @TestTable TABLE (ID INT PRIMARY KEY, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL, Col3 int CHECK (Col3>=18)) INSERT INTO @TestTable VALUES(1,'Value1',12 , 20) TRUNCATE TABLE @TestTable |
The table variable structure cannot be changed after it has been declared
According to this tip interpretation, the following query has to return an error:
|
1 2 3 4 5 6 7 |
DECLARE @TestTable TABLE (ID INT PRIMARY KEY, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL) ALTER TABLE @TestTable ADD Col4 INT |
The table variable in SQL Server should use an alias with the join statements
If we want to join two or more table variables with each other or regular tables, we have to use an alias for the table names. The usage of this looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @Department TABLE (DepartmentID INT PRIMARY KEY, DepName VARCHAR(40) UNIQUE) INSERT INTO @Department VALUES(1,'Marketing') INSERT INTO @Department VALUES(2,'Finance') INSERT INTO @Department VALUES(3,'Operations ') DECLARE @Employee TABLE (EmployeeID INT PRIMARY KEY IDENTITY(1,1), EmployeeName VARCHAR(40), DepartmentID VARCHAR(40)) INSERT INTO @Employee VALUES('Jodie Holloway','1') INSERT INTO @Employee VALUES('Victoria Lyons','2') INSERT INTO @Employee VALUES('Callum Lee','3') select * from @Department Dep inner join @Employee Emp on Dep.DepartmentID = Emp.DepartmentID |
The table variable does not allow to create an explicit index
Indexes help to improve the performance of the queries but the CREATE INDEX statement cannot be used to create an index for the table variables. For example, the following query will return an error:
|
1 2 3 4 5 6 7 8 |
DECLARE @TestTable TABLE (ID INT PRIMARY KEY, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL) CREATE NONCLUSTERED INDEX test_index ON @TestTable(Col1) |
However, we can overcome this issue with the help of the implicit index definitions because the PRIMARY KEY constraint or UNIQUE constraints definitions automatically create an index and we can use these INDEX statements in order to create single or composite non-clustered indexes. When we execute the following query, we can figure out the created index which belongs to @TestTable:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @TestTable TABLE ( Col1 INT NOT NULL PRIMARY KEY , Col2 INT NOT NULL INDEX Cluster_I1 (Col1,Col2), Col3 INT NOT NULL UNIQUE ) SELECT ind.name,type_desc FROM tempdb.sys.indexes ind where ind.object_id=( SELECT OBJECT_ID FROM tempdb.sys.objects obj WHERE obj.name IN ( SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE (COLUMN_NAME = 'Col1' OR COLUMN_NAME='Col2' OR COLUMN_NAME='Col3') )) |
Conclusion
In this article, we explored the table variable in SQL Server details with various examples. Also, we mentioned the features and limitations of the table variables.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023