Generate identity value is incorrect after SET IDENTITY_INSERT ON on an empty table - by MauriD

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 492452 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 9/27/2009 2:47:41 AM
Access Restriction Public


If you manually insert a value into an IDENTITY column in an empty table the next identity-generated number is not 1 but 2.
If you manually insert a value int an IDENTITY column when the table IS NOT empty, everything works fine.
Sign in to post a comment.
Posted by Tobias [MSFT] on 12/14/2009 at 12:27 AM
Hi Mauri,

This issue will be fixed in the next major release of SQL Server.

Thanks for reporting it!
- Tobias, SQL Server Engine
Posted by Microsoft on 10/5/2009 at 9:41 AM
I am not sure if we will address this but we will take a further look at the reasoning behind this.
Posted by MauriD on 9/30/2009 at 1:07 PM
I agree with you, but nontheless this is a bug and so I reported it
Posted by Paul A Nielsen on 9/30/2009 at 8:55 AM
This is not a concern, Surrogate keys should not have an implied meaning.
Posted by Sankar Reddy on 9/27/2009 at 2:12 PM
Interesting observation. Here is the script I used to reproduce the problem.

IF OBJECT_ID('dbo.IdentityInsertTest', 'U') IS NOT NULL
    DROP TABLE dbo.IdentityInsertTest
CREATE TABLE dbo.IdentityInsertTest (Id INT IDENTITY(1,1))

SET IDENTITY_INSERT dbo.IdentityInsertTest ON
INSERT INTO dbo.IdentityInsertTest (Id) VALUES (-999)
SET IDENTITY_INSERT dbo.IdentityInsertTest OFF
SELECT * FROM dbo.IdentityInsertTest
SELECT * FROM dbo.IdentityInsertTest