Hugo Kornelis (5/16/2012)

---

Nice question. I knew about implicit transaction, but I had to do some digging because I couldn't remember if primary key violation errors are batch-aborting or not.

(One possible -slight!- improvement to the question would have been to explicitly mention that the three insert statements are executed as a single batch; the current wording can be interpreted as them being executed one by one).

have not tested if the code as posted would actually work or complain about the column reference in the column constraint. If it does work, I would classify it as a parser bug, since the description in BOL implies that the seperating column between columns and table constraints is mandatory.

CREATE TABLE QODT1(Col1 int NOT NULL,col2 CHAR(1) NOT NULL,col3 VARCHAR(20)
       CONSTRAINT PK_QODT1 PRIMARY KEY 
       (col1))

Thomas Abraham (5/16/2012)

---

Thanks for the question.

Got it wrong, and I even ran a test. Code below:

CREATE TABLE #Temp(Col1 INT NOT NULL
   CONSTRAINT PK_QOTD_1 PRIMARY KEY (Col1))
   
INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (1)
INSERT #Temp(Col1) VALUES (2)
GO

SELECT * FROM #Temp 

(not exactly the same code as the question, but functionally equivalent) Only got one row back from SELECT, as I had anticipated.

So, what have I got wrong here?

Hugo Kornelis (5/16/2012)No idea what went wrong. I copied and pasted the code above, hit execute, and got two rows back. SQL Server 2012. What version are you running?

Thomas Abraham (5/16/2012)

---

SQL Server 2008

Hugo Kornelis (5/16/2012)The only way I can reproduce what you are seeing, both on SQL Server 2008 R2 (I don't have 2008 vanilla) and on SQL Server 2012, is by setting the SET XACT_ABORT option to ON. (It is OFF by default).
Are you running this from a connection where you previously executed a SET XACT_ABORT ON command? Or from a client that implicitly emits this command upon connecting?

Thomas Abraham (5/16/2012)

---

I was using a fresh connect that does not emit this command. Is there a command on the order of sp_configure that I can run to verify?