Showing posts with label temp table. Show all posts
Showing posts with label temp table. Show all posts

Monday, March 4, 2013

Temp table vs Table variable: Sql Server

http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table

major difference:

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.

Important:

  • Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

  • Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

  • Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article