(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").