Shows how table variables keep data even after a transaction rollback

TableVariableRollbackExample.sql

BEGIN TRAN;

DECLARE @MyHoldingTable TABLE (id int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL);

INSERT INTO dbo.CustomersTableIdent (FirstName, LastName)
    OUTPUT inserted.id, inserted.FirstName, inserted.LastName
    INTO @MyHoldingTable (id, FirstName, LastName)
        VALUES ('My first name (removed)', 'My last name (removed)');

ROLLBACK

SELECT id, FirstName, LastName FROM @MyHoldingTable;