Know What Table Variables are ?
SQL Server support features that allow users to work with temporary data that gets stored in temporary tables. Microsoft introduced table variables for the first time with SQL Server 2000 as an alternate option to using temporary tables. Table variable is a type of variable data that are used to store temporary data. It has been improved with the release of SQL Server 2014 version.
It is defined by using DECLARE keyword:
DECLARE @Table1 TABLE
(
ID INT,
Name NVARCHAR (30)
)
Table variables can also be created by a predefined table type that is a table definition stored in the database as an object (user defined table type):
Creating User defined Table Type---
CREATE TYPE dbo.Table1 AS TABLE
(
ID INT,
Name NVARCHAR (30)
)
Declaring table variable---
DECLARE @Table1 AS dbo.Table1
Insert data into table variable---
INSERT INTO @Table1(ID, Name) VALUES(1, ‘Jennifer’)
Table Variables VS Temporary Tables
Table Variables differs from Temporary Tables in some ways stated as below:
Transaction Logs are not recorded for the table-variables. E.g., when we rollback transaction, table variable will retain its value and wont revert back to the old value.
Any Procedure with a temporary table cannot be precompiled whereas execution plan with table- variables can be compiled statically in advance increasing execution speed.
Like variables, the lifetime of Table Variables lies only within the same scope. Unlike Temporary Tables, table variables are not seenin inner stored procedures & in Exec statements. Moreover,it cannot be used in INSERT/EXEC statements.
Table Variables in SQL Server 2014
Table Variables are used when user needs to work with small temporary data, for passing a list of values to stored procedures/functions for auditing purpose. We will discuss how the table variable functions in several areas:
1. Table variables in SQL Server transactions
Table Variables are not affected by a rollback transaction. The table variable gives the value of the data that was modified during the transaction even if the transaction is rolled back.
Let us take an example using Temporary table as&T and Table Variable as%T
CREATE table #T (avarchar(50))
DECLARE @T table (avarchar(50))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @’
BEGIN transaction
UPDATE #T set s='new value #'
UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s
---------------
old value #
s
---------------
new value @
In the above example, we first assign each tables with same ‘old value’ string and we begin the transaction to update the contents. After transaction, both will contain same ‘new value’ string. However, when we rollback the transaction, the table variable retains its value ‘new value’ string instead of reverting to the ‘old value’ string. Though the table variable was alteredinside the transaction, it is not considered a part of transaction itself.
2. Passing Table variables to SQL Server stored Procedures & Functions
When we need to pass a list of values to stored procedures or functions, it is not easy to do with the single parameters especially when the list contains many attributes. This can be fulfilled by using input parameter like XML type, but it is not advisable, as it requires more work and XML needs to be parsed in stored procedure.
With the introduction of table variable, it can be passed to stored procedures & functions as a parameter solving the above stated problem.
We will see the example of using table variables as a parameter in stored procedures. We need to define the appropriate table type before passing list of parameters.
We will assume that the dbo.UserRole table stores user-role mapping in which user can have only one role and should be updated periodically by a provided list.
We create a stored procedure that takes the updated user-role mapping as parameter & updates the table data.
Assuming table variables passed to a stored procedure are READONLY. We cannot do any DML operations (insert, update, delete) on a table variable inside the stored procedure or function. In the below code, we will examine the call of stored procedure with a table-valued parameter.
Updated table looks like this:
Indexing SQL Server Table Variables
While indexing table variables, indexes can be created only in table definition and after declaration, it cannot be altered. However, with the introduction of SQL Server 2014, non-unique & composite indexes can be created. Following examples define ways to declare table variable in SQL Server 2014:
Limitations of SQL Server table Variable:
Table variable cannot be altered after its creation. Therefore, we need to make sure that we choose the right structure for the table variable and necessary indexes/ constraints are created in table definition.
SQL Server does not maintain statistics for table variables. So while using large temporary data set, it is advisable that we use temporary tables instead of table variables to improve performance.
We cannot use user-defined functions in the table definition. CHECK & DEFAULT constraints, COMPUTED COLUMNS can be created on table variables in definition.
While initializing a table variable, we cannot use SELECT INTO or INSERT EXEC Statements.
Table Variables can be passed to stored procedures and functions only as READONLY.
Conclusion
Table Variables can be of good help for users working with small temporary data as they allow passing a list of values to stored procedures/functions making development simpler. Though it provides many features of regular tables like indexes, constraints etc. but they have some limitations which are stated earlier. Table variables retain the present value after the rollback transaction that can be very useful depending on user’s requirements.