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.