Tuesday, March 17, 2009

Temporary tables Vs Table variables

Temporary Tables vs. Table Variables
temporary tables: create table #T (…)And
table variables: declare @T table (…)

first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

s --------------- old value #
s --------------- new value @==================================================

No comments: