SQL Server : Table Variables

1. What are table variables

Table variables - A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

Table variables are tied to the temdb just like temporary tables. They are faster than temporary tables. They are scoped to the batch in which they are declared.

One of the key differences that allows sql server to manage a table variable with much lower overhead compared to a temp table is the smaller, well defined scope of table variables. Temp Tables are scoped to a connection (/stored proc) while table variables are always scoped to a query batch.

The table data type can be used in place of temporary tables stored in the tempdb database.

2. Advantages of the Table variables

1. Table variables, like any other variable, have very well defined scope, which is the function, stored procedure, or batch in which it is declared. Thus they are cleared from memory when the calling program (Sp or function or trigger) exits.

2. We don’t have to write Create Table and Drop table statements in Sps.  These Steps take time as various catalogues have to be updated.

3. Table variables result in fewer recompilations of a stored procedure as compared to temporary tables. As can be noted easily, stored proc Recompilations take a major chunk of time. Again, there are various other factors which influence recompilations. (detailed reference available at the end of this note)

4. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources

5. because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks

3. Is it all good

There are certain, though few restrictions on what can be done with  Table variables:

1. Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.

2. Table variables cannot be used in a INSERT EXEC or SELECT INTO statement.  E.g. We can’t write:  Insert into @tblVar Exec testower.spBPI..

3. The table definition cannot be changed after the initial DECLARE statement.

4. Syntax of Table variables

1. Create Table variable

Declare @tmpTblOppFilters Table (

          IntOpportunityId int Not Null,

          IntCompanyId int,

          dtCreated datetime default getdate ()

)

2. Insert, Select, Update, Delete syntax is similar to Tables.
3. Query modifications to replace Temp Tables with table variables :-

Suppose we have the following query, involving a temp table.

Select Col1, Col2

Into #tempp

From tbl1

This can be changed to use a table variable as follows:-

-- This is a table variable declared

Declare @tmpTbl table (intCol1 int, intCol2 int)   

--Insert records into table variable.

Insert into @tmpTbl              

Select Col1, Col2

From tbl1

4. Please refer: Reference 8.1 for complete syntax on table variables.

5. Queries

SP Recompilation:

In the sample stored procedure replacing the temporary table with a TABLE variable allows us to eliminate the CREATE TABLE and the CREATE INDEX statements. These changes cut the number of recompiles down to one. Here is the procedure with the SET statements grouped at the beginning of the procedure and #Temp1 converted to a TABLE variable:

CREATE PROCEDURE spRecompiles_Just_Once AS

DECLARE @Temp1 TABLE ([id] int identity (1, 1)

                   , [name] varchar (64))

SET NOCOUNT ON -- Doesn't cause a recompile

-- Cause a recompile because of a change in a session option

SET ANSI_NULLS OFF

SET ANSI_WARNINGS OFF

INSERT INTO @Temp1 ([name]) VALUES ('Gregor Samsa')

INSERT INTO @Temp1 ([name]) VALUES ('Padme Amidala')

INSERT INTO @Temp1 ([name]) VALUES ('Casper Guttman')

SELECT * FROM @Temp1 ORDER BY [name]

GO

Please note that there is still one recompile every time the procedure is executed.

6. The prescription

In general, use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. Generally speaking, if the number of records is not large, then the table data type will be faster than using a temp table. But if the amount of data is large, then a temp table most likely will be faster.

Hence the guideline is to always use table variables except in the following scenarios:

1. There is demonstrable overall performance gain from the usage of one of the features (like non-clustered indexes) that table variables don’t support. Such gain should be tested for and verified before release of code that uses temp tables.
2. Even though no specific feature is used, due to sheer volume of data a temp table outperforms the table variable, again this must be verified with a realistically sized test data set if you wish to use a temp table.
3. You will utilize the temp table across multiple query batches within a single connection and find usage of table valued functions inefficient to meet your objective.

7. Related Best practices

1. If you have to use a temp table:

·         Only include the columns and rows you actually need in the table, no more.

·         Do not use SELECT INTO to create your temp table, as it places locks on system objects. Instead, create the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.

·         Consider using a clustered and non-clustered indexes on your temp tables, especially for very large temp tables. You will have to test to see if indexes help or hurt overall performance.

·         When you are done with your temp table, delete it to free up tempdb resources. Don't wait for the table to be automatically deleted when the connection is ended

2. Table variable, Temp table and cursors

·         Table variables are preferred over temp tables and cursors

·         If you have to choose between temp tables and cursors, temp tables are generally preferred.

8. Reference

1. Table Variables Explained : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp
2. Table Variables  FAQ : http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b305977
3. Query Recompilation in SQL Server 2000 : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
4. SQL Server 2000: New Features Provide Unmatched Ease of Use and Scalability to Admins and Users : http://msdn.microsoft.com/msdnmag/issues/0800/sql2000/default.aspx