We've all heard about differences between temporary tables and table variables in SQL Server.
They include performance, storage in memory or disk, tempdb use, etc.
But the biggest and mostly overlooked difference is:
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
select * from #temp
select * from @var
ROLLBACK
select * from @var
if object_id('tempdb..#temp') is null
select '#temp does not exist outside the transaction'
We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.
This little known fact can be very handy in a bunch of different scenarions.
I showed one with auditing here.
