What is the difference between temp table and table variable?
- 8 Answers
- 24615 Views
- Microsoft, I also Faced
- E-Mail Answers
Answer Posted / jerry joseph
SYNTAX
Temporary Tables: CREATE table #T (…)
Table Variables: DECLARE @T table (…)
Table Variables are out of scope of the transaction mechanism.
If you make changes to a Temp Table inside a Transaction and
Rollback the Transaction those changes will be lost.
Changes made to Table Variable inside a Transaction will
remain even if you Rollback the Transaction
Any procedure with a temporary table cannot be pre-compiled
An execution plan of procedures with table variables can be
statically compiled in advance
Table Variables exist only in the same scope as variables.
They are not visible in inner stored procedures and in
exec(string) statements
Table variables are in-memory structures that may work from
2-100 times faster than temp tables.
Access to table variables gets slower as the volume of data
they contain grows.
At some point, table variables will overflow the available
memory and that kills the performance.
Use table variables only when their data content is
guaranteed not to grow unpredictably; the breaking size is
around several thousand records.
For larger data volumes, use temp tables with clustered
indexes.
| Is This Answer Correct ? | 13 Yes | 4 No |
Post New Answer View All Answers
Explain different types of index?
What is the use of for clause?
Explain what are sparse columns?
How many categories of data types used by sql server?
how do you determine the Load performance of any query in sql server {example how do u determine performance of a select stmnt which returns Dynamically many no of records ... some times 100,1000,10000 etc., }
How to optimize stored procedure optimization?
How many types of local tables are there in sql server?
How to run sql server 2005 books online on your local system?
What is query processing?
what is a sub-report?
what is raid and what are different types of raid configurations? : Sql server database administration
What you can do to delete a table without the delete trigger firing?
Explain error and transaction handling in sql server?
What are the differences between union, intersect, and minus operators?
What are the types of stored procedures in an sql server?
