Transactions Don't Work the Way You Think

(Just a little note for myself; sometimes I want to explain XACT_ABORT, but I never have the example fresh in my mind.)

We were all trained that a transaction is a "all-or-nothing" thing: either all the statements succeed and are all committed, or none are committed. It doesn't work that way by default in SQL Server, though:

SET XACT_ABORT OFF     -- This is the default.
--SET XACT_ABORT ON    -- This is NOT the default.

CREATE TABLE DatabaseProperty
BEGIN TRANSACTION

INSERT INTO DatabaseProperty
 (sName, sValue)
VALUES
 ('Fred', 'This is tooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo long!')

INSERT INTO DatabaseProperty
 (sName, sValue)
VALUES
 ('Barney', 'Will be added if XACT_ABORT is OFF, which is the default!')

COMMIT

This is why XACT_ABORT is usally set to ON, instead the default ("OFF").