Tuesday, June 9, 2015

SQL - Table Variables

This feature was released with SQL Server 2000 and used as the alternative of the Temporary table. Table variables don’t participate in transactions or lockingThey are carried out as system transactions. Table variables are used to store temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 
They have the same feature as any normal data table but we cannot create non-clustered indexes on them.

Table variable will no longer exist after the procedure exits because there will be no table to clean up with a DROP statement.
We are free to use table variables in batches, stored procedures, and user-defined functions (UDFs) just like any local variable you create with a DECLARE statement. We can UPDATE records in our table variable as well as DELETE records. We cannot use ALTER command on Table variable.

SQL Server does not maintain statistics on a table variable and statistics are used heavily by the query optimizer to determine the best method to execute a query. Table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries due to statistics restrictions.
Scope- Table variable will no longer exist after the procedure exits because there will be no table to clean up with a DROP statement. You can use table variables in batches, stored procedures, and user-defined functions (UDFs) just like any local variable you create with a DECLARE statement. We can UPDATE records in our table variable as well as DELETE records.

Table variables could not be used as input or output parameter but can return a table variable from a UDF.
Performance- Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.
We cannot use a table variable with SELECT INTO or INSERT EXEC queries but this feature is available in temp tables.

----- syntax to declare @Employee as table variable
Declare @Employee as table
(
EmpId Int,
EmpName Varchar(25),
EmpAge int,
EmpDept varchar(6)
)
----- how to insert data into table variable
Insert into @Employee  values
(1,'ABC',21,'D001'),
(2,'DEF',22,'D011'),
(3,'GHI',23,'D021'),
(4,'JKL',24,'D031')
----- get result from the table variable
select
EmpId,
EmpName,
EmpAge,
EmpDept
From @Employee

EmpId
EmpName
EmpAge
EmpDept
1
ABC
21
D001
2
DEF
22
D011
3
GHI
23
D021
4
JKL
24
D031


For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key.

Limitations:
  • Additionally table variables do not support INCLUDE columns, filtered indexes or partitioning.
  • Queries that insert into (or otherwise modify) table variables cannot have a parallel plan.
  • Table variables can be used inside scalar or multi-statement table UDFs.
  • Table variables cannot have named constraints.
  • Table variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT.
  • Table variables do not support table hints such as WITH (FORCESCAN).
  • CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.

3 comments:

  1. Would these best be used for small datasets that are only going to be used for a single procedure? What's your experience with speed if the table is too large? What would you define as large?

    ReplyDelete
    Replies
    1. I agree with your thoughts to use table variable for small datasets. I usually use what's more convenient at that time and experiment a bit. For the large datasets, I always prefer temp tables.

      Delete
  2. I don't think table variables do not carry statistics however, making them not such a great choice for query plan optimization, especially with large data-sets.

    ReplyDelete

Popular Posts