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.
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:
The above statement will show the following error:
This statement will display the following error:
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: