What is the difference between temp table and table variable?
- 8 Answers
- 25600 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
How to use order by with union operators in ms sql server?
What is transaction server consistency?
What is exporting and importing utility?
Can a stored procedure call itself or a recursive stored procedure? How many levels of sp nesting is possible?
What is database black box testing?
What is a constant or literal in ms sql server?
What are the ways available in sql server to execute sql statements?
How do I find the default sql server instance?
What is the difference between stored procedure and functions?
Do you know what is user defined datatypes and when you should go for them?
What are the steps you must follow to hide sql server instances?
What is an identity column in insert statements?
What is query optimizer in sql server?
Explain few examples of RDBMS?
What the different components of Replication and what is their use?
