Increment of integer PK values by 1 doesn't throw duplicate key error. Why? As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity after each row. What happens inside? Is this part of the standard of just SQL Server specific behavior? Thanks!

DECLARE @T TABLE(
    x INT PRIMARY KEY
);

INSERT INTO @T VALUES (1)
INSERT INTO @T VALUES (2)

UPDATE @T SET x = x + 1;

-- DUPLICATE KEY ERROR EXPECTED.

SELECT * FROM @T;

--- VALUES HAVE BEEN INCREMENTED. NO ERROR THROWN.
The checks (about UNIQUE, PRIMARY KEY and other, NOT NULL and CHECK constraints) are done after the whole UPDATE statement has finished. This is the expected behaviour in SQL. Operations/statements are performed on sets (of rows), not on each row individually.(*)

And while for NOT NULL and CHECK constraints, it doesn't matter if the checking is done after each row change or after the whole statement, for unique constraints (UNIQUE and PRIMARY KEY) it does matter. 

In fact, the SQL standard has 2 options. The checks are either done after the statement (when the constraint is NOT DEFERRABLE) or even later, at the end of the transaction (when the constraint is DEFERRABLE) and the statement is part of a transaction.

SQL-Server supports only NOT DEFERRABLE constraints, so checks are after every statement and not at the end of transactions.



(*)
Perhaps you have been used to MySQL, which shows this non-standard behaviour, checking constraints after each row update (or insert.) See the SQL-Fiddle where the update fails (if you change the DBMS from SQL-Server to MySQL.)