While writing stored procedures it is often needed to store data temporarily in one place and perform some manipulations on it so that the expected output can be acquired. For example, when the result set of a complex query pulling data from one or more database tables, we need a table-like object to store them in order to reuse it wherever we need it. Microsoft SQL server has provided two kinds of database objects to store data temporarily. They are temporary tables and table variables.
Now you may raise questions like can’t we use the permanent tables for this purpose? Why should we use temporary tables and table variables? Of course we could create a permanent table that’s needed to store the temporary data and drop the table when it is not needed any more, but it uses a little more resources than these temporary objects do for constraints, security, logging, locking, rollback etc. Using permanent tables for this purpose may result in performance overhead.
Next, why are there two kinds of temporary objects for the same purpose? Both temporary tables and table variables have their pros and cons, you have to identify the appropriate one for you to use depending on your requirement and the situation. Let’s have a look at some aspects that could help you choose the suitable solution.
When you use a temporary table in your stored procedure, they are created in the tempdb database of Microsoft SQL Server. Temporary tables exist in the tempdb for the lifetime of the user session, or the stored procedure that created it, and are dropped automatically after their lifetime. There are two types of temporary tables. One is Local and the other is Global (i.e. global temp tables can be shared with many users where the local temp tables cannot). Later we will discuss the syntax to create local and global temp tables.
Table variables are local scope variables that are visible within the stored procedure or function which uses them, and are cleared when the store procedure/ function finishes its execution. One of the limitations of table variables is that they cannot be passed through as input/output parameters to other stored procedures or functions. However, temp tables can be used as parameters to other stored procedures or functions.
Table variables are suitable if the data to be stored is considerably small. Otherwise, replace them with temp tables to save memory space. When a temp table is used in a stored procedure, each session executing this stored procedure has its own copy of this temp table. If the temp table is intended to hold large amounts of data, then you may be better off going with a global temp table, so that multiple users can use the same temp table.
Temporary tables also consume some resources for logging and locking functionalities, where as table variables provide very little of these functionalities. Thus, ROLLBACK transactions could affect a temp table, but have no effect on table variables.
Let’s look into the syntaxes needed to create temp tables and table variables. First the syntax to create local temp table:
- CREATETABLE #MyTempTable (Col1 INT, Col2 VARCHAR(10))
To insert values into the above temp table,
- INSERTINTO #MyTempTable SELECT 1,'Apple'
- INSERTINTO #MyTempTable SELECT 2,'Orange
If you want to create a Global temp table then just use ‘##’ instead of ‘#’ in the table name as shown below.
- CREATETABLE ##MyGlobalTemp (Col1 INT, Col2 VARCHAR(10))
The syntax to create a table variable:
- DECLARE @MyTableVar TABLE(Col1 INT, Col2 VARCHAR(10))
To insert into the table variable,
- INSERTINTO @MyTableVar SELECT 1,'Apple'
- INSERTINTO @MyTableVar SELECT 2,'Orange'
Now that we have an idea on the syntaxes, we shall discuss a few more limitations of table variables over temp tables.
- ALTER, TRUNCATE and DELETE commands are not applicable to table variables.
- Indexes cannot be added to table variables explicitly. However, System Indexes can be created using PRIMARY KEY and UNIQUE KEY constraints.
- Table variable columns cannot be generated dynamically as shown below
- SELECT * INTO @MyTableVar FROM MyTable
We have just looked into some of the aspects that must be considered while using either a temp table or a table variable, but there may be few more points I may have forgotten to mention here. Please feel free to leave a comment if you can think of any. In conclusion, it is up to you, the developer, to analyze the requirements and the environment where it is to be used, and choose the best one for your needs.