Reader Level:
Articles

Temporary Table vs Temporary Variable in SQL Server

By Venkatesan Jayakantham on June 11, 2010
We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.
  • 0
  • 0
  • 14648

We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.

Temporary table is transaction dependent and it abides to the database transaction whereas temporary variable is not transaction bound.    
 
Sample Query:
 
---Temporary table---

drop table #temp
create table #temp (id int, val varchar(100))
begin tran ins
insert into #temp values (1,'Venkat')
rollback tran ins
select * from #temp
 
We are not getting any records indicating the temporary table will bound to the transaction strategies.

---Temporary variable---

declare @tempval table(id int, val varchar(100))
begin tran ins
insert into @tempval values (1,'Venkat')
rollback tran ins
select * from @tempval
 
Even we have provided rollback transaction. Records are available in the table variable.

1.gif

Venkatesan Jayakantham

I am Venkatesan Prabu Jayakantham(SQL Server DBA and Dotnet developer) from India (Tamil Nadu). A Microsoft SQL Server MVP,MCITP,MCTS,MCAD,CCNA,QAI certified Expert.  I am a Mindcracker MVP for the year 2010. &nbs... Read more

COMMENT USING

Trending up