- 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 variable is not created in memory. It is created in
Table variable declared will have a different SQL engine name inside the
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.
Table variable can not be dropped explicitly.
Table variable can not be truncated.
Table variable can be used in
DELETEstatement. But it can not be used in
SELECT * INTO.
You can not create explicit index on the table variable. When declaring the table variable, you can define the column as
UNIQUE. This will create an implicit index.
CHECKconstraint, 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/OFFcan 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 the
RECOMPILEhint. So that the optimizer will use the correct cardinality for the table variable.