SQL Server Table Variables Overview

In the article, we are going to explore the basics of SQL Server table variables, compare local temporary table vs global temporary vs table variable, and examine a query execution plan performed with dbForge Studio for SQL Server.

In the article “Introduction to SQL Server Temporary Tables”, we have reviewed temporary tables and defines their differences from table variables.

Overview of SQL Server Table Variables

A table variable is a SQL Server data type used to store temporary data which is similar to a temporary table.

The peculiarities of table variables are as follows:

1. A table variable is available in the current batch query only.
2. A table variable cannot change its definition.
3. It is not necessary to delete a table variable directly.
4. If some transactions add changes to a table variable, these changes are not rolled back during the transaction rollback.
5. Statistics are not gathered for a table variable by default.
6. The following syntax describes how to declare a table variable:

DECLARE @tbl TABLE…;

To move on, we will exemplify how to create a temporary table, populate it with test data, and recompile a table variable.

Create a SQL Server Temporary Table

Now, we are going to create a MyLocalTempTable temporary table with a primary key for the ID field and two non-clustered indexes – ix_InsertUTCDate and ix_Ind for the InsertUTCDate and Ind fields respectively. Then, we fill this table variable with test data. 

To output the contents on the screen, use the following code fragment:

DECLARE @MyLocalTempTable TABLE (
  [ID] INT PRIMARY KEY,
  [Value] NVARCHAR(255),
  [Ind] INT,
  [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),
  INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),
  INDEX ix_Ind NONCLUSTERED ([Ind])
);
sri
INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])
SELECT 1, N'177', 1
UNION ALL
SELECT 2, N'355', 1
UNION ALL
SELECT 3, N'777 ID', 2;

SELECT *
FROM @MyLocalTempTable
WHERE [Ind]=1;

The actual execution plan of the query is as follows:

In the actual execution plan, the scanning is performed on the clustered index. Note that:

• Actual Number of Rows Read and Estimated Number of Rows to be Read values are not identical.
• Actual Number of Rows for All Executions and Estimated Number of Rows Per Execution values do not match either.

Thus, the statistics are absent. More precisely, in table variables, there will always be only one row. Such behavior of table variables will not allow developing an optimal execution plan for a large amount of data.

However, if we apply the RECOMPILE option, it will calculate the statistics, and the actual execution plan will become optimal:

DECLARE @MyLocalTempTable TABLE (
  [ID] INT PRIMARY KEY,
  [Value] NVARCHAR(255),
  [Ind] INT,
  [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),
  INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),
  INDEX ix_Ind NONCLUSTERED ([Ind])
);

INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])
SELECT 1, N'177', 1
UNION ALL
SELECT 2, N'355', 1
UNION ALL
SELECT 3, N'777 ID', 2;

SELECT *
FROM @MyLocalTempTable
WHERE [Ind]=1
OPTION (RECOMPILE);

As before, we can see the scanning of the clustered index in the actual plan. Note that:

• The Actual Number of Rows Read and Estimated Number of Rows to be Read values match now.
• The Actual Number of Rows for All Execution and Estimated Number of Rows Per Execution values are close to matching.

It means that the stats are more relevant.

Statistics are not created for table variables by default. To clarify this, execute the following code fragment and analyze the actual execution plan for the latest selection: 

DECLARE @MyLocalTempTable TABLE (
  [ID] INT PRIMARY KEY,
  [Value] NVARCHAR(255),
  [Ind] INT,
  [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),
  INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),
  INDEX ix_Ind NONCLUSTERED ([Ind])

);

INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])
SELECT 1, N'177', 1
UNION ALL
SELECT 2, N'355', 1
UNION ALL
SELECT 3, N'777 ID', 2;

SELECT [Ind]
FROM @MyLocalTempTable
WHERE [Ind]=1
OPTION (RECOMPILE);

As we can see, the actual plan uses Index Seek for the ix_Ind non-clustered index for the Ind field instead of scanning according to the clustered index.

Due to the RECOMPILE option, the Actual Number of Rows Read and Estimated Number of Rows to be Read values almost match, as well as the Actual Number of Rows for All Execution and Estimated Number of Rows Per Execution values. This shows that the statistics are more relevant. 

However, the table variables statistics are irrelevant by default. When we apply the RECOMPILE option, the stats get closer to the actual values but still differ significantly. As a result, with data increasing in a table variable, the execution plan will further stray from the optimal one. 

Let’s execute the following code fragment:

SELECT *
FROM @MyLocalTempTable
WHERE [Ind]=1
OPTION (RECOMPILE);

BEGIN TRAN
	UPDATE @MyLocalTempTable
	SET [Value]=NULL
	WHERE [Ind]=1;

	SELECT *
	FROM @MyLocalTempTable
	WHERE [Ind]=1
	OPTION (RECOMPILE);
ROLLBACK TRAN

SELECT *
FROM @MyLocalTempTable
WHERE [Ind]=1
OPTION (RECOMPILE);

The output is as follows:

This script outputs all the rows of the table variable that have Ind=1. Then, in the transaction, the Value fields get updated in all those rows. They get the NULL values, and we output them again. After that, the transaction is rolled back, and we output all the rows of a table variable with Ind=1 once again. The outcome states that the transaction rollback does not cancel the changes made in the table variable.

Therefore, when we implement changes in a table variable of a transaction and then roll that transaction back, the changes will remain. This differentiates table variables from usual and temporary tables.

Starting from the 2019 version, SQL Server memorizes the actual parameter values of the previously made queries according to their actual execution plans. If the first query without the RECOMPILE option is non-optimal in execution, all the subsequent execution plans for the same or similar queries will be optimized.

Note: For code formatting, we used the SQL Complete tool.

Local and global temporary tables vs table variables

Now, we recap everything we have found out so far in a table:

Thus, temporary tables suit better for any data volume processing and transaction rollbacks. Table variables are better as logs for transaction rollbacks. Table variables can also be used to process small data volumes.

Overview of the Actual Query Execution Plan in dbForge Studio for SQL Server

In dbForge Studio for SQL Server, the actual query execution plan looks as follows:

We can see the actual query execution plan on the left. It is a hierarchical tree structure containing the following elements in each block:

1. Plan Diagram – graphical view of the execution plan
2. Plan Tree – tree-view of the execution plan
3. Top Operations – top operations based on load, including CPU load
4. Table I/O – input/output operations for tables
5. Plan XML – XML view of the execution plan

On the right, we get a detailed overview of each element. 

For more information about query execution plan, see dbForge Studio for SQL Server and SQL Server Execution Plans. 

Conclusion

To sum up, we have reviewed the basics of SQL Server table variables and temporary tables, compared the differences between local temporary table, global temporary, and table variable, and exemplified the procedures with a query execution plan.

To find out more about the way to delete local temporary tables, read our next article.