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

A temporary table is transaction dependent and it abides by the database transaction whereas a 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 transactions. Records are available in the table variable.

Results