Reader Level:
Article
SQL Server

Temporary Table vs Temporary Variable in SQL Server

By Venkatesan Jayakantham on Jun 11 2010
We have seen lot of difference between temporary variable and temporary table. Here is a nice difference in Transaction perspective.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 22k
  • 0

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