Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between temp table and table variable?
- 8 Answers
- 26843 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 does normalization do to data?
How to delete database objects with "drop" statements in ms sql server?
What are the differences between decimal and float in ms sql server?
Is it true that rules do not apply to data already existing in a database at the time the rule is created?
Are there issues when exporting SSRS reports into Microsoft Excel? When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be?
Define compound operators?
Which rendering formats are affected by the pagesize properties?
What is the user of Primary key?
What are the joins in sql server? : sql server database administration
Can we call future method from trigger?
How to encrypt data between dialogs?
Define model database?
What is the default Port No on which SQL Server listens?
Can you force a query to use a specific index?
What are different types of statement?
