Monday, September 19, 2011

Temp Table Vs Table Variable

Temp tables behave same as normal tables and are bound by transactions. Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. 

Transaction logs are not recorded for the table variables, hence they are out of scope of the transaction mechanism. While Transaction logs are recorded for the temporary table.

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. 

Table variables don't participate in transactions, logging or locking. This means they're faster.

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option

You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

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.

No comments:

Post a Comment