SQL Tip: The Importance of Changing Schema

SQL Tip: How to share passwords over email

SQL Tip: Change coloring on status bar of SSMS

New SQL Tip - SET XACT_ABORT ON

I have a new SQL Tip about SET XACT_ABORT.  If you're unfamiliar with this setting, you should definitely spend three minutes and watch this tip:



CREATE TABLE t1(
col1 INT PRIMARY KEY)

GO

INSERT INTO t1
VALUES
(1)
,(2)
,(3)
,(4)-- Assuming SET IMPLICIT_TRANSACTIONS is OFF

INSERT INTO t1
VALUES(1)

INSERT INTO t1
VALUES(99)
-- Which rows get inserted?
SELECT * FROM t1-- Which rows get inserted for this explicit transaction?
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (100)
COMMIT TRANSACTION;
GO-- Which rows get inserted?  Did 100 get inserted?
SELECT * FROM t1-- With SET XACT_ABORT?
SET XACT_ABORT ON;
BEGIN TRANSACTION;
   INSERT INTO t1
   VALUES
   (1)

   INSERT INTO t1
   VALUES
   (200)
COMMIT TRANSACTION;
GO
DROP TABLE t1