Temporary Table vs Temporary Variable in SQL Server

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


Similar Articles