IDENTITY_INSERT - Setting Value resets next identity?

Question

I'm not too familiar with SQL Server, but I'm working on a script that explicitly sets values on inserts on tables with IDENTITY ON. Due to some poorly planned schemas - it looks like I'm going to have to explicitly insert to some arbitrarily high number (eg 10,000).

On future INSERTs (not INDENTIY_INSERTS), will it be inserted to 10,001? Or the lowest "free" slot?

For example - table currently has rows 1-50, and I do an IDENTITY_INSERT that looks like this

SET IDENTITY_INSERT [dbo].[ConditionPathways] ON
INSERT INTO [dbo].[ConditionPathways] ([ConditionPathwayID], [ConditionSegmentID], [WorkflowDefinitionID]) 
VALUES (10000, 10000, 10000)
SET IDENTITY_INSERT [dbo].[ConditionPathways] OFF

Will the next "normal" insert, place it at 10001 or 51? I'm really hoping its 51.

Answer 1

After you have turned IDENTITY_INSERT off, the next identity available will be 10001.

You could reseed the identity field using DBCC CHECKIDENT with the RESEED option to set the next identity back to 51. The problem in doing this is that when the table fills up to the point where the last inserted item had an identity of 9999, the next item will either result in a duplicate identity or an error, depending on whether you have a unique constraint on the identity column.