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.