Table variables don't roll back

A standard question asked at DBA interviews is what is the difference between a temp table and a table variable.

Aside from the usual incorrect answer of one being a physical table in tempdb and the other being just held in memory, and the less often heard but correct fact of one taking the collation of tempdb and the other taking the collation of the current database, a little known aspect is that one of them is totally unaffected by transactions.

So what I hear you say ...

Combine this with TRY and CATCH error logic, and you can capture the data causing transactions to roll back and write it somewhere else after the transaction has failed.

Don't believe me??  OK, have a look at this:

DECLARE @Data TABLE (MyID INT)


BEGIN TRANSACTION 
INSERT @Data VALUES (1)
ROLLBACK TRANSACATION

Now in a normal, or even temp, table you would get no records back from a subsequent SELECT statement, but run SELECT * FROM @Data after the ROLLBACK statement in this situation, and you will see it returns the data added within the transaction.

Obviously this is a very basic example, but using this within complicated transactions, and the possibilities for auditing and error handling are endless!  Well ... nearly endless ... well ...