Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.


YOU ARE HERE: HOME Questions When should i go for temporary tables and table data types in SQL Server Which one is advantage

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

View(s): 20018

When should i go for temporary tables and table data types in SQL Server? Which one is advantage compared to other in terms of performance while writing queries?

Answer 1)
There are three major theoretical differences between temporary tables and table variables

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism.

The second major difference is that 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.

Finally, 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.

Irrespective of above differences, if data load is less, @table (table type variable) will work better than #table (temp table) and if data load is more, #table will work better @table.
  Asked in:  TCS (Tata Consultancy Services)   Expertise Level:  Experienced
  Last updated on Friday, 25 November 2011
4/5 stars (11 vote(s))

Register Login Ask Us Write to Us Help