SQL Server TSQL Table Variables

Table Variables In SQL Server: These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped. Like with temp tables, table variables also reside in TempDB. They are also generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table variables can have a primary key, but no indexes can be created on them. They don’t have any statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimizer has no way of knowing the number of rows in the table variable.

Syntax to create Table variable is

It is similar to any variable where declare variable  and set/insert values into it for Temporary storage like a temp table.


declare @Temp table (ID int, Name varchar(128))


Table Variables are like Temporary tables but have some difference which are highlighted in my article http://sqlserver-dba.co.uk/cte/cte-temp-table-temp-variables.html