Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Thursday, February 07, 2008 9:15 AM | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET