Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
ProblemThere are some features in SQL Server which allow developers to work with temporary data. One of them is a table variable. It has been improved in SQL Server version by version and in SQL Server 2014 it has become quite flexible. To work with table variables more confidently and to be able to identify the appropriate tasks they can be applied to, we need to fully understand table variables, their features, limitations, restrictions and application areas.
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 @[email protected] --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
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.
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
Last Update: 2015-10-16
About the author
View all my tips