Table Variable in SQL Server

Table variables are a special part of the local variable that allows us to hold complete table records temporarily. They are similar to the temporary tables in SQL Server. It is first introduced by Microsoft in SQL Server 2000 as an alternative for temp tables. Usually, the table variable supports all the properties of a local variable, but unlike temporary or standard tables, local variables have some limitations.

The declaration of table variables is similar to the local variables. The name of a table variable starts with the @ symbol, which has a type of table. Its declarations can also define column names with data types and constraints. We can also use table variables in the SELECT, INSERT, UPDATE, and DELETE statements within their scope. We cannot create table variables using the INTO clause in a SELECT statement, unlike permanent or regular tables.

Syntax

The following are the syntax to declare the table variable in SQL Server:

In the syntax, we will first write the table variable name after the DECLARE keywords. We must ensure that the name of table variables begins with the @ symbol. Next, we will define its structure as a standard table, including column names, data types, size, and constraints.

Table variable scope

SQL Server can declare the table variables within batches, functions, and stored procedures. Table variables go out of the scope at the end of the batch, similar to local variables. When we declare table variables in the function or stored procedure, table variables will also go out of the scope after the stored procedure or function exits.

Table Variable Example

Let us understand how we can declare and work with a table variable in SQL Server using some examples. The below example declares a table variable named @months_table, which consists of three columns: number, month, and name:

After declaring the table variable, we will insert data into this using the INSERT statement:

We can query data from the table variables using the SELECT statement as a temporary table. We should note that we will execute the whole batch at the same time. Otherwise, SQL Server through an error message "Must declare the table variable @Months_Table." Now, execute the below statement:

The below image shows the partial output:

If you want to update any data into table variables, you must use the below statement:

Executing the whole batch, we can see that the second row will be updated successfully:

Storage Location of Table Variables

SQL Server always stored the table variables in the tempdb database. As we know that the table variable's lifecycle begins at the declaration point and ends when the batch's end. So it will be automatically dropped in SQL Server at the end of the batch. We can understand this concept with the help of below example:

Executing the above batch together will display the two result set. We can see them in red rectangle and blue rectangle, respectively.

The red rectangle output shows the column name, schema name, and data types of the defined table variable, and the blue rectangle output doesn't show any data. It is because of the first INFORMATION_SCHEMA.COLUMNS statement is executed together with the table variable. Therefore, SQL Server will display the information of the table variable @Months_Table from the tempdb database. On the other hand, the second INFORMATION_SCHEMA.COLUMNS statement will not show any data regarding the @Months_Table. The reason behind this is that the GO statement ends the batch, and the table variables lifecycle is terminated. Thus, it proves that the tempdb database is a storage location of a table variable in SQL Server.

Restrictions on Table Variables

Table variable has some restrictions in SQL Server. Some of them are described below:

• Once the structure of a table variable is defined during declaration time, we cannot alter the structure of a table variable as a standard or temporary table. For example:

The above statement will show the following error:

• We know that statistics are useful for the query optimizer in constructing an effective query execution plan. Unfortunately, it doesn't provide the table variables. As a result, table variables cannot be used for storing a large number of rows.
• SQL Server does not allow table variables to works with input or output parameters, unlike other data types. On the other hand, a user-defined function can return a table variable.
• Table variables don't work with non-clustered indexes, so we cannot create them in table variables. However, the implicit index definitions can solve this problem. The reason behind this is the SQL Server can create an index automatically by using the PRIMARY KEY constraint or UNIQUE constraint definitions, and we can use these INDEX statements to create single or composite non-clustered indexes.
• Table variables do not allow us to use the FOREIGN KEY constraint.
• Table variables cannot work with the TRUNCATE statement to delete all data from the table. For example:

This statement will display the following error:

• If we use table variables with the JOIN, we are required to use an alias for the table names to execute the queries. For example:

Transactions and table variable

Transaction in SQL Server is a single unit of logic used for managing the CRUD (insert, select, update and delete) operations. We can start an explicit transaction using the BEGIN TRANSACTION statement and complete it with COMMIT or ROLLBACK statements. Let us execute the below query and then analyze their result:

In this output, we can see that modified data cannot erase the table variables because here, CRUD operations are managed by explicit transactions.

Table Variables in Stored Procedure

A stored procedure is a group of one or more pre-compiled SQL statements into a logical unit. Each procedure in SQL Server always contains a name, parameter lists, and Transact-SQL statements. The following example will explain how we can use table variables in stored procedures.

The above statement creates a stored procedure named 'SP_Months_Table' where we have used table variables also. We can call this procedure using the EXEC statement. See the below output:

Table Variables in User-defined Functions

A function defined by a user to perform a certain specific task is referred to as user-defined functions. A user will create this type of function for his own requirements. The following example will explain how we can use table variables in user-defined functions.

The above statement creates a user-defined function named 'UDF_TableVariable' where we have used table variable also. We can call this function using the below statement:

We will get the below output:

Some key differences of Table Variables over Temporary Table

The following are the key differences between a table variable and a temporary table:

• When we use table variables in a stored procedure instead of a temporary table, we get fewer recompilations.
• A table variable in SQL Server will use fewer resources than a temporary table.
• Table variables use fewer resources in terms of locking and logging overhead than temporary tables so that we can use them whenever possible.
• Table variables cannot perform the truncate operation, whereas a temporary table can perform.
• When the table variable uses transactions, it cannot roll back the data. On the other hand, the temporary table can roll back the data with a transaction operation.
• Table variables are stored in the tempdb database rather than in memory like the temporary table.