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 locking. They 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.

To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable