Peter Larsson Blog

Patron Saint of Lost Yaks

Curiosity found, the wrap

After a good nights sleep when almost all pieces fit together here
weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx

I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint.

It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes.

DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY)

INSERT  @Items
SELECT  1 UNION ALL
SELECT  2

SELECT 
FROM    @Items

INSERT  @Items
SELECT  1

INSERT  @Items
SELECT  2

INSERT  @Items
SELECT  3

SELECT 

FROM    @Items