Exploring SQL Server 2014 Table Variables

By: Sergey Gigoyan   |   Updated: 2015-10-16   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | > Temp Tables

Problem

Solution

A table variable is a variable data type which can be used to store temporary data. It's defined using the DECLARE keyword and the table's structure is defined in the declaration as shown below:

DECLARE @TestTable TABLE
(
	ID INT,
	Name NVARCHAR(40)
)
	

Table variables can also be created by a predefined table type which is a table definition stored in the database as an object (User Defined Table Type) as shown below:

--Creating User Defined Table Type
CREATE TYPE dbo.TestTable AS TABLE
(
  ID INT,
  Name NVARCHAR(40)
)

--Declaring table variable
DECLARE @TestTable  AS dbo.TestTable

--Inserting data into table variable
INSERT INTO @TestTable(ID, Name) VALUES(1, 'Abc')
	

There is a common misconception that table variables are in memory objects. In reality they are stored in the tempdb database like temporary tables. Like regular variables, table variables are visible only within the batch where they were created. Table variables can be used for working with small temporary data, for passing a list of values to stored procedures or functions, for auditing, etc.

In this tip we will illustrate how table variables behave in transactions, how to pass as a parameter to a stored procedure, how to create indexes on table variables (they can be created only in the table definition) and table variable limitations.

Table Variables in SQL Server Transactions

Unlike temporary tables, table variables are not affected by a rollback. As regular variables, they keep the data which was modified during the transaction even if the transaction is rolled back. Let's see this behavior with an example:

--Creating table variable
DECLARE @TestTable TABLE
(
	ID INT,
	Name NVARCHAR(40)
)

--Creating temporary table
CREATE TABLE #testTable
(
	ID INT,
	Name NVARCHAR(40)
)

DECLARE @id INT = 0

BEGIN TRANSACTION 

    SET @id = 5

	INSERT INTO @TestTable (ID, Name) 
	VALUES (1, 'Name1'), (2, 'Name2')

	INSERT INTO #testTable (ID, Name) 
	VALUES (1, 'Name1'), (2, 'Name2')

ROLLBACK

--Selecting data after rollback
SELECT @id AS '@id'

SELECT * FROM @TestTable

SELECT * FROM #testTable
	

As we can see, the data inserted into the temporary table is rolled back, but the regular variable and the table variable kept the data that was modified during the transaction:

Thanks to this feature table variables can be used for auditing purposes. For example, when we are updating a table and the transaction is rolled back and we want to audit these uncommitted changes, we can store it in a table variable and after the rollback then insert this data into a table for auditing.

Passing Table Variables to SQL Server Stored Procedures and Functions

Sometimes it's necessary to pass a list of values to stored procedures or functions. It's not easy to do using single parameters, especially when the list contains a lot of attributes. These kind of tasks can be solved by using an input parameter as an XML type, but this is not a perfect solution, because it requires additional work (XML has to be parsed in the stored procedure).

Starting with SQL Server 2008, we can pass table variables to stored procedures and functions as a parameter and this feature solves the previously mentioned problem. Passing table variables as a parameter provides a flexible programming model and makes the process of passing the list of parameters to a stored procedure or a function easier and logically correct.

Now we will illustrate with a simple example how to use table variables as a parameter in stored procedures. Before passing a list of parameters to the procedure we should define the appropriate table type.

--Defining table type
CREATE TYPE dbo.UserRole AS TABLE
(
  UserID INT,
  RoleID INT
)
	

Let's assume that we have a simple dbo.UserRole table which stores User-Role mapping and should be periodically updated by a provided list (it is assumed that user can have only one role).

CREATE TABLE dbo.UserRole 
(
  UserRoleID INT IDENTITY(1,1),
  UserID INT,
  RoleID INT
  CONSTRAINT IX_UserRole_UserID UNIQUE NONCLUSTERED (UserID ASC)
)


INSERT INTO dbo.UserRole  (UserID, RoleID) 
VALUES (1,2),(2,5),(3,2),(4,6)
	

Here we are creating a stored procedure which accepts the updated user-role mapping as a parameter and updates the table data:

CREATE PROCEDURE uspUpdateUserRoleMapping
@pUserRoleList UserRole READONLY
AS
BEGIN 
	MERGE dbo.UserRole AS target
	USING @pUserRoleList  AS source 
	ON target.UserID = source.UserID
	WHEN MATCHED THEN 
	UPDATE SET target.RoleID = source.RoleID
	WHEN NOT MATCHED THEN
	INSERT (UserID, RoleID)
	VALUES (source.UserID, source.RoleID);	
END
	

Take into account that table variables passed to a stored procedure are READONLY. Table variables must be passed as READONLY and we can't perform DML operations (INSERT, UPDATE, DELETE) on a table variable in the body of stored procedure or function. In the following code we illustrate the call of our stored procedure with a table-valued parameter:

--Table before update
SELECT * FROM dbo.UserRole 

--Declaring table variable
DECLARE @userRoleList UserRole

--Generating updated data
INSERT INTO @userRoleList(UserID,RoleID)
VALUES (1,4), (5,5)

--Applying new user-role mapping update
EXEC uspUpdateUserRoleMapping @pUserRoleList=@userRoleList

--Table after update
SELECT * FROM dbo.UserRole 

As a result we can see the updated table:

Indexing SQL Server Table Variables

There is a limitation when indexing table variables. Indexes can be created only in the table definition, moreover after declaration, table variables can't be altered. Starting with SQL Server 2014, non-unique and composite indexes can be created. Here are some examples of what you can do.

--Declaring table variable with primary key
DECLARE @TestTable1 TABLE
(
	ID INT PRIMARY KEY,
	Name NVARCHAR(40)
)

--Declaring table variable with unique key
DECLARE @TestTable2 TABLE
(
	ID INT PRIMARY KEY ,
	Name NVARCHAR(40) UNIQUE
)

--Declaring table variable with unique clustered index
DECLARE @TestTable3 TABLE
(
	ID INT PRIMARY KEY NONCLUSTERED,
	Name NVARCHAR(40) UNIQUE CLUSTERED
)

--Declaring table variable with nonclustered, non-unique index ( Starting from SQL Server 2014 )
DECLARE @TestTable3 TABLE
(
	ID INT PRIMARY KEY,
	Name NVARCHAR(40),
	INDEX IX_TestTable3_Name NONCLUSTERED (Name)
)


--Declaring table variable with composite nonclustered index ( Starting from SQL Server 2014 )
DECLARE @TestTable4 TABLE
(
	ID INT PRIMARY KEY,
	FirstName NVARCHAR(40),
	LastName NVARCHAR(40),
	INDEX IX_TestTable4_FirstName_LastName NONCLUSTERED (FirstName, LastName)
)

--Declaring table variable with more than one index ( Starting from SQL Server 2014 )
DECLARE @TestTable5 TABLE
(
	ID INT PRIMARY KEY,
	FirstName NVARCHAR(40),
	LastName NVARCHAR(40),
	INDEX IX_TestTable5_FirstName NONCLUSTERED (FirstName),
	INDEX IX_TestTable5_LastName NONCLUSTERED (LastName)
)
	

SQL Server Table Variable Limitations and Restrictions

It's important to know the table variable restrictions and limitations when working with them. One of these restrictions has been already mentioned above: table variables can't be altered after creation, so we should consider this fact and choose the right structure for the table variable and create the necessary indexes and constraints in the table definition.

Secondly, SQL Server doesn't maintain statistics for table variables, so to improve performance, it is suggested to use temporary tables instead of table variables when working with large temporary data set.

CHECK and DEFAULT constraints, COMPUTED COLUMNS can be created on table variables (in the table definition), however unlike regular tables we can't use user-defined functions in their definition.

We can't use SELECT INTO or INSERT EXEC statements to initialize a table variable.

Table variables can be passed to stored procedures and functions only as READONLY.

In queries where table variables are referenced outside a FROM clause, they must be referenced by an alias as shown below:

--Declaring table variableDECLARE @userRoleList UserRole

SELECT url.UserID, url.RoleID --Is referenced using an alias
FROM
@userRoleList url --Alias for table variable must be defined, when referenced outside a FROM clause
INNER JOIN
dbo.UserRole ur
ON ur.UserID=url.UserID --Is referenced using an alias
	

Conclusion

Table variables are a good solution for working with small temporary data, also they allow us to pass a list of values to stored procedures and functions which makes some development needs much simpler. They have many features of regular tables such as indexes, constraints, computed columns, etc.  However, they have some restrictions: they can't be altered after creation, statistics are not maintained, etc.  Like regular variables they are not affected by rollback in transactions which could be a big plus depending on your requirements.

Next Steps

Review these tips for more information:

• Differences between SQL Server temporary tables and table variables
• Improving SQL Server performance when using table variables
• SQL Server Temp Table vs Table Variable Performance Testing
• https://msdn.microsoft.com/en-us/library/ms175010.aspx
• https://msdn.microsoft.com/en-us/library/ms188927.aspx
• https://msdn.microsoft.com/en-us/library/bb510489.aspx

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips