What is the difference between temp table and table variable?

Answers were Sorted based on User's Feedback



What is the difference between temp table and table variable?..

Answer / balasubramaiam s( ramco system

Table variables are Transaction neutral. They are variables
and thus aren't bound to a transaction.

Temp tables behave same as normal tables and are bound by
transactions.

BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )

insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

select * from #temp
select * from @var

ROLLBACK

select * from @var
if object_id('tempdb..#temp') is null
select '#temp does not exist outside the transaction'


We see that the table variable still exists and has all
it's data unlike the temporary table that doesn't exists
when the transaction rollbacked.

Is This Answer Correct ?    13 Yes 0 No

What is the difference between temp table and table variable?..

Answer / 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

What is the difference between temp table and table variable?..

Answer / 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

What is the difference between temp table and table variable?..

Answer / nalla

Temporary variables logs very less into transaction log, so
compare to Temp tables, temp varaibles are good in
performance wise.

Is This Answer Correct ?    2 Yes 0 No

What is the difference between temp table and table variable?..

Answer / rathikapoobalan

1. If we use Temporary Table in a stored procedure, We
should drop it at the end.
It is not necessary in the case of Table variable.

2. In Table variable we can hold maximum of 20000 records
only. If it exceeds, we can use temporary table

Is This Answer Correct ?    11 Yes 16 No

What is the difference between temp table and table variable?..

Answer / nandkumar karlekar

we can create the index on temp table which leads better
performance in join and searching .

temp table can be used in exec statements.

for Table variable above two point are not applicable

Is This Answer Correct ?    0 Yes 7 No

What is the difference between temp table and table variable?..

Answer / samba shiva reddy . m

temp tables physically exists in temp DB
but temp variables does not exists.

Is This Answer Correct ?    4 Yes 13 No

What is the difference between temp table and table variable?..

Answer / prakash

Temp table create automatically when you create any data
table create you must mention there create temporary table.
Table variable is must declare in procedure when you crate
procedure.

Is This Answer Correct ?    1 Yes 13 No

Post New Answer

More SQL Server Interview Questions

What is Full Outer Join?

3 Answers  


What is the difference between HAVING clause and the WHERE clause?

7 Answers  


What are sql servers used for?

0 Answers  


what are file groups?

3 Answers   Evalueserve,


why SQL server is more used to store database rather than Access

2 Answers  






Is sql different from sql server?

0 Answers  


Explain what is meant by replication of database?

0 Answers   Cap Gemini,


How many levels of sp nesting is possible?

0 Answers  


hi i am working as a testengineer , so i want to no the backend data base connection can any one tell mwe in detail

0 Answers   IntraLogic, Oracle,


How to convert numeric expression data types using the cast() function?

0 Answers  


How to start and end transact-sql statements?

0 Answers  


Do you know sql server 2008 backup compression?

0 Answers  


Categories