Summer SQL Teaser #10 ROLLBACK

This one is not so much a teaser but it will show you what you can do in case you want to insert data in a logging table after a rollback occurs
Without running this try to guess what the counts of the three tables will be after the rollback


CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @Test table (id int)



BEGIN TRAN
INSERT INTO @Test VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN


SELECT '@test',COUNT(*) FROM @Test
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test


DROP TABLE Test,#Test