Temporary Table Variables in SQL Server

In a previous article, we explored Temporary tables in SQL Server. We can also declare Temporary Table Variables which like Temporary Tables, allow us to store temporary data. However there are some differences between the two which we will be seeing shortly.

Let’s first create a simple Temporary Table variable and insert, select and update the data from the same. The script is as shown below –



Execute the above script as a single batch and you will see the desired output. The declaration of the table variable is done with the help of ‘@’ sign.

Now try the script queries one by one. First declare the table variable. Then try to insert the data and you will receive an error as shown below –



The life time of the temporary table variable is only within the batch. So it has more limited scope compared to temporary tables.

There are other limitations which you will have to understand while working with table variable compared to temporary tables. Some of them are described below –

• You cannot use Select * INTO statement with Table variables.
• You cannot use sp_help stored procedure to see the metadata of table variables like we saw for temporary table in our previous article. However, if you want to see the metadata for the table variable, you can make use of Sys.Tables and Sys.Columns in the context of TempDB database.
• When you are creating table variables using Dynamic SQL, all the statements must be declared with the dynamic statement scope. Unlike temporary table, where we can declare first and then use dynamic SQL to update, insert or select the data from the table; table variables will only work within the scope of dynamic SQL batch only.
• If you create a table variable with Identity column, you cannot add the identity column value explicitly by using “SET IDENTITY_INSERT <TableName/TempTableName> ON/OFF”.