Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between temp table and table variable?
- 8 Answers
- 27397 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
What are the types of user defined functions in sql server?
How to check if a table is being used in sql server?
What is executereader?
In one interview...interviewer ask me question pleas tell me sql server architecture.. can any body tell me the sql server architecture with digram
How display code or Text of Stored Procedure using Sql query in Sql Server ?
What are the components of sql server service broker?
How can I tell if sql server is 32 or 64 bit?
Why SQL Agent is used?
What command is used to delete a table from the database in the sql server and how?
Can we delete data from a view?
whats new about truncate in sql server 2008?
Write a query to include a constraint, to check whether the employee salary is greater than 5000?
What is resultset concur_updatable?
Can anyone tell that the extra features are there in SQL SERVER 2008 that are not available in previous versions .
Tell me what is de-normalization and what are some of the examples of it?
