http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/06/20/use-table-variables-in-transaction/ Printed 2013/12/11 04:14PM
use Table Variables in transactionwhen 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,
insert into @MyTableVar
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.