use Table Variables in transaction

when using table variable, please note table variable doesn't support transaction rollback. here is the words from BOL

Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.


let's do a testing:
==============
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);

begin tran
insert into @MyTableVar
values(1,2,3,getdate())

rollback

select * from @MyTableVar
==============

well, we still get the result returned even if we rollback the transaction. the only place this feature might be useful is for log. we can log what happen during transaction rollback by table variable.

another thing might cause big issue is:
table variable don't have statistics, so don't anticipate it can get good enough execution plan if you insert a lot of data into table variable.