Some facts about Table Variables and Temp Tables are:
- You can create local and global temporary tables. Local temporary tables (#table_name) are visible only in the current session, and global temporary tables (##table_name) are visible to all sessions.
- Table Variables and Temp Tables both use the tempdb database.
- Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
- Temp tables behave same as normal tables and are bound by transactions.
- Assignment operation between table variables is not supported.
- Clustered indexes can be created on both table variables and temporary tables.
- Both are logged in the transaction log.
- Temporary tables cannot be partitioned.
- Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.
|#temp table||@table variable|
|You can truncate a temp table.||You cannot truncate a table variable.|
|You can alter temp table.||Table variables cannot be altered after they have been declared.|
|One of the most valuable assets of a temp table is the ability to add either a clustered or non-clustered index.||You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like.|
|Temp tables can be used in the following situations: |
INSERT #temp EXECsp_someProcedure
SELECT * INTO #temp FROM someTable
|You cannot use a table variable in either of the following situations: |
INSERT @table EXECsp_someProcedure
SELECT * INTO @table FROM someTable
|You cannot create temp tables inside user-defined function.||You can declare table variable inside user-defined function.|
|Temp tables allow for the auto-generated statistics to be created against them.||The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics|
|Statistics generated on temp table, help the optimizer to determine cardinality.||A table variable will always have a cardinality of 1, because the table doesn'texist at compile time.|
|Temp tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE.||You cannot drop a table variable when it is no longer necessary. They are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.|
|Temp table can be referenced by its name or by an alias, except in the FROM clause. |
e.g.: SELECT id FROM #t1 t INNER JOIN #t2 ON t.id = #t2.id
|Table variables must be referenced by an alias, except in the FROM clause. |
e.g.: SELECT id FROM @foo f INNER JOIN #foo ON f.id = #foo.id
|Temp tables are visible to the calling procedure in the case of nested procs.||Table variables are not visible to the calling procedure in the case of nested procs.|
|Use of temp table in stored procedure can cause stored procedure recompilations.||Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used|
|Temp tables are involved in SQL transactions.||Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.|
|Temp tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.||Table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan.|
|Only static tables and temporary tables support the statement "SET IDENTITY_INSERT ON".||The table variable does not support the statement "SET IDENTITY_INSERT ON".|