Interested to Buy Any Domain ? << Click Here >> for more details...
What is the difference between temp table and table variable?
- 8 Answers
- 27143 Views
- Microsoft, I also Faced
- E-Mail Answers
Answer Posted / yugal
1) Table variables don't participate in transactions,
logging or locking. This means they're faster as they don't
require the overhead, but conversely you don't get those
features.
2) Procedure with a temporary table cannot be pre-compiled,
while an execution plan of procedures with table variables
can be statically compiled in advance. Pre-compiling a
script gives a major advantage to its speed of execution.
This advantage can be dramatic for long procedures, where
recompilation can be too pricy
3) Table variables exist only in the same scope as
variables. Contrary to the temporary tables, they are not
visible in inner stored procedures and in exec (string)
statements. Also, they cannot be used in an insert/exec
statement.
4) You can create a temp table using SELECT INTO, which can
be quicker to write (good for ad-hoc querying) and may
allow you to deal with changing datatypes over time, since
you don't need to define your temp table structure upfront.
5) You cannot truncate a table variable but temporary table
can be truncated.
6) You cannot drop a table variable when it is no longer
necessary—you just need to let it go out of scope but
temporary table can be dropped.
7) Table variables cannot be altered after they have been
declared but temporary table can be altered.
| Is This Answer Correct ? | 6 Yes | 3 No |
Post New Answer View All Answers
What is sql server english query?
How adventureworkslt tables are related?
How to drop an existing user defined function in ms sql server?
What is amo? : sql server analysis services, ssas
What is transaction server auto commit?
What is use of attributehierarchyenabled? : sql server analysis services, ssas
What are the differences between clustered and non-clustered index?
What is the name of the Database which IBM mainframe uses?
How to connect a database with sql express.?
What is sub-query in sql server?
Relational calculus is what type of language?
How many files can a database contain in sql server?how many types of data files exists in sql server? How many of those files can exist for a single database?
what's the maximum size of a row? : Sql server database administration
what is normalization? Explain different levels of normalization? : Sql server database administration
What is a functions and types in sql server?
