Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between temp table and table variable?
- 8 Answers
- 27179 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 in brief about Microsoft SQL server?
How many types of schemas are there?
Tell me about pre-defined functions of sql?
what are the different ways of moving data/databases between servers and databases in sql server? : Sql server database administration
What is the preferred way to create a clustered and non-clustered index? Which index should you create first the clustered or non-clustered?
What are magic tables in sql server?
What are cascading parameters in ssrs reports?
What are the types of dml?
How does using a separate hard drive for several database objects improves performance right away?
What is row_number()?
Explain alternate key, candidate key and composite key in sql server?
How to modify an existing stored procedure in ms sql server?
Does sql server 2016 have ssms?
How to list all schemas in a database?
What is 2nf normalization form?
