What is the difference between temp table and table variable?
- 8 Answers
- 24676 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
Explain the benefits of user-defined functions?
What is the difference between sql server 2000 object owner and sql server 2005 schema? : sql server database administration
How to use "begin ... End" statement structures in ms sql server?
what is the Ticketing tool used in Wipro technologies at Bangalore...???
Define the one-to-one relationship while designing tables.
Do you know nested transaction?
What is it’s similarity with sql server?
Explain few examples of stored procedure over triggers?
What is the difference between TRUNCATE and DROP?
what are the different stages of Report Processing?
What is 1nf normalization form?
When do you think a developer should use sql server-based cursors?
Can you explain important index characteristics?
Explain filtered indexes benefits?
What is repeatable read?
