CODEWITHSUNDEEP
sqlsql-serverdatabase
Jerrold
Jerrold

Reputation: 1564

IDENTITY_INSERT - Setting Value resets next identity?

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.

Upvotes: 6

Views: 7163

Answers (3)

Richard Vivian
Richard Vivian

Reputation: 1750

You can on an insert statement set the reseed value to the range that the table is expecting using the following: (Example Customer Table Used)

Given that the max currenct CustomerId in the table is 10 before the insert below.

SET IDENTITY_INSERT CUSTOMER ON

INSERT INTO Customer    
(CustomerID , FirstName, LastName, Title, DoB, Email, IsActive, DTInserted , DTUpdated)
VALUES
(10000, 'U','V','W','1/1/2000','1/1/2000',0,GETDATE(), GETDATE())
SET IDENTITY_INSERT CUSTOMER OFF

DECLARE @Reseed AS INT
SET     @Reseed = (SELECT MAX(CustomerID) FROM Customer WHERE CustomerID < 10000)
DBCC CHECKIDENT('Customer', RESEED,@Reseed)

The next insert into the customer table will have a CustomerId of 11

Upvotes: 1

Will Charczuk
Will Charczuk

Reputation: 919

did a sample thing in MSSQL server 2008r2

drop table identityTest_tbl

Go

create table IdentityTest_tbl (id int IDENTITY(1,1) primary key not null, name varchar(10) not null);

go

set identity_insert identityTest_tbl on;
insert into IdentityTest_tbl (id, name) values (10, 'a');
insert into IdentityTest_tbl (id, name) values (11, 'b');
set identity_insert identityTest_tbl off;

GO

insert into IdentityTest_tbl values ('c');

go

select * from IdentityTest_tbl

unfortunatley, it looks like 'c' is inserted with an identity of 12

Upvotes: 1

adrianbanks
adrianbanks

Reputation: 82944

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.

Upvotes: 16

Related Questions