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).
Prior to submitting the question, I tested as a batch, then executing one insert statement at a time. Made no difference to what happens.
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.
Retested the CREATE TABLE statement not more than 2 minutes ago
CREATE TABLE QODT1(Col1 int NOT NULL,col2 CHAR(1) NOT NULL,col3 VARCHAR(20)
CONSTRAINT PK_QODT1 PRIMARY KEY
(col1))
It worked when I initially created the question, and it works today.
L' Eomot Inversé
In answer as to why I cited as a reference, the statement for SQL 2000, sort of a tongue in cheek action on my part, to show people how long the IMPLICIT TRANSACTIONS feature/rule has been in effect.