Monday, June 2, 2014

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