[WAT] Fun with table variables

Run this code.  Did you expect it to act a certain way?  Why?  Because table variables.  Thanks to (crap, who was it? *sigh*) who pointed this out in his tempDB talk a year or so ago.


Update 2016/08/15 - I heard a good use for this at #SQLSatSA!   Use it as part of an ETL.  When it fails and rolls back, you know which batch you were on.  : D

DECLARE @blah TABLE (fielda VARCHAR(20))
INSERT INTO @blah
        (fielda)
SELECT 'a'
UNION ALL
SELECT 'b'
SELECT * FROM @blah

BEGIN TRANSACTION
UPDATE @blah
SET fielda = 'c'
SELECT * FROM @blah
ROLLBACK TRANSACTION

SELECT * FROM @blah