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.
Difference:
#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". |