Esat Erkec
Table variables are stored in the tempdb database

The Table Variable in SQL Server

December 3, 2019 by

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:

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:

Update and delete statment usage for table variable in SQL Server

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

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:

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.

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:

Constraint usage for table variables in SQL Server

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:

Alter statement error for table variable

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:

Table variables transaction mechanisim details

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:

TRUNCATE statment and  table variables in SQL Server

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:

Altering the structure of the table variables.

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:

Join statement usage with table variables in SQL Server

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:

How to create index for table variables in SQL Server

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:

Index structure of the table variable in SQL Server

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.

Esat Erkec
168 Views