Monday, July 23, 2012

#temp table Vs @table variable

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".