Be Conscious on Table Variables and Temp Tables

In SQL Server when you want to store table of data for later usage either a Temp Tables or a Table Variable will come in to play. But are you really conscious about making the decision? about what is the right fit for your situation. Most of the time when I ask someone why it is a table variable used here, people mostly have no reason. So in this article I am going to describe the contrasting usage of the two type of temporary storage in SQL Server.

Table Variables

Table variables are a variable declared as a table. They are stored in both memory and in the disk. They can only be accessed within the batch and scope in which they are declared.  Table variables cannot be dropped explicitly. Once the batch execution is finished, the Table Variables are dropped automatically.

When the Table variable is declared it’s structure can not be modified later. In simple terms DDL commands can not be run on Table variables.

Below is the syntax of creating a table variable;

DECLARE @Customer TABLE
(
Id INT,
Name VARCHAR(100),
DateJoined DATETIME
);

Temporary Tables

Mainly there are two types of Temporary Tables. Namely Local Temporary Tables and Global Temporary Tables.

• Local Temporary Table: These temporary tables are valid only to the session it was created. After the session terminates these are removed from the temp db. However unlike table variables Local Temporary Tables can be deleted explicitly.
• Global Temporary Table: These tables are available for all the sessions and users. They are not deleted until the last session using them is terminated. Similar to local Temporary Table, a user can delete them explicitly.

Both the above two types of temporary tables are stored in side the temp DB. Unlike table variables, structure of a temporary table can be changed after it’s creation.

Below is the syntax of creating a temporary table;

CREATE TABLE #Customer (Id INT,Name VARCHAR(100),DateJoined DATETIME);

When to use table variables

Table variables are best suit for below scenarios;

1.  Generally if the data you are going to load in to the table is only few rows table variables will be a better choice.
2. If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. This is because table variables does not belong to a transaction.
3. If the best query plan for the query using the table will not every change then you should use a table variable. By using a table variable in this scenario will skip the overhead of statistic creation and recompilation.
4. If the table data is populated from a potentially expensive SELECT statement then using a table variable will block the possibility of this using a parallel plan.

 When to use Temporary Tables

1. If you are going to load thousands of rows to the table then temporary table is a better choice.
2. If you want to change your table structure after creating the table you have no other option but to use a temporary table.
3. If you need an index that cannot be created implicitly through a UNIQUE or PRIMARY KEYconstraint then you need a #temporary table as it is not possible to create these on table variables.
4. If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables).

Conclusion

The Temporary Table and Table Variables can be both strong and weak depending on the scenario presented. We hope this article will give you enough information for you to make a correct conscious decision.