SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Temp table vs Table variable: Sql Server

on March 4, 2013

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

Advertisements

One response to “Temp table vs Table variable: Sql Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: