Table Variable in SQL Server
- Table variable syntax:
declare @my_table_variable table(
id int,
name varchar(50)
);
select * from @my_table_variable;
go
Table variable is a local variable with a special data type
table
.Table variable is not created in memory. It is created in
temp_db
.Table variable declared will have a different SQL engine name inside the
temp_db
.Table variable will become out of scope once the batch ends. If you try to access after the batch separator
GO
, you will get an error to declare the table variable.Table variable can be declared inside a user defined function (UDF) and DML operation can be executed on table variable. But temporary table can not be created inside an UDF.
Limitations
Table variable can not be dropped explicitly.
Table variable can not be truncated.
Table variable has limited scope and are not part of the persistent database, transaction rollbacks don't affect them.
Table variable can be used in
SELECT
,INSERT
,UPDATE
, andDELETE
statement. But it can not be used inSELECT * INTO
.You can not create explicit index on the table variable. When declaring the table variable, you can define the column as
PRIMARY KEY
orUNIQUE
. This will create an implicit index.CHECK
constraint, DEFAULT value, and computed columns in table variable can not call a UDF.You can not create a named constraint.
You can not use user defined data type in column definition.
If you are using nested stored procedure, the table variable is not visible to the calling stored procedure (outer sp) unlike temporary tables.
If you create a table variable column with identity. You can not insert explicit value in identity column. Because
SET IDENTITY INSERT ON/OFF
can not be used.You can not create a table variable in dynamic SQL.
Table variable will have a cardinality of 1. So statistics are not tracked for them and this result in bad query plan.
Queries that modify the table variables don't generate the parallel query execution plans. Performance can be affected when large table variables or table variables in complex query is modified. In this scenario, temporary table is a better alternative.
Mostly , optimizer will build a query plan on the assumption that table variable has no rows. So, if the table variable is going to have more than 100 records, then temporary table is the best option.
If table variable is going to be used in
JOIN
, use theRECOMPILE
hint. So that the optimizer will use the correct cardinality for the table variable.