What is the difference between temp table and table variable?
- 8 Answers
- 24617 Views
- Microsoft, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What are the built in functions in sql server?
Can a stored procedure call itself or recursive stored procedure? How many levels sp nesting is possible?
What happens if you insert a duplicate key for the primary key column in ms sql server?
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
What is index in an assignment?
What are the types of ssrs?
What is spid in sql server profiler?
What is forward - only cursors / read only cursor?
What is enhanced database mirroring in sql server 2008?
Explain the categories of stored procedure?
What are the advantages of using third-party tools?
What are the different authentication modes in sql server?
Does partitioning help performance?
What is t-sql script to take database offline – take database online.
What are the disadvantages of using the stored procedures?
