You need to design a stored procedure that contains a transaction. If an error condition causes the transaction to roll back, a temporary result set must be available to the query that follows the failed transaction.
Which temporary storage object should you use?
A. a temporary table
B. a table variable
C. a common table expression (CTE)
D. a derived table
Correct Answer: B
Explanation/Reference:
The table variable doesn’t participate in transactions. See example below:
DECLARE @MyTable TABLE
( MyIdentityColumn INT IDENTITY(1,1),
MyCity NVARCHAR(50))
INSERT INTO @MyTable (MyCity) VALUES (N’Boston’);
BEGIN TRANSACTION
INSERT INTO @MyTable (MyCity) VALUES (N’London’)
ROLLBACK TRANSACTION
INSERT INTO @MyTable (MyCity) VALUES (N’New Delhi’);
SELECT * FROM @MyTable mt
Read more: http://www.sqlservercentral.com/blogs/steve_jones/2010/09/21/table-variables-and-transactions/