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.


5
3
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

Description

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 Microsoft 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
@Paul
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
GO
CREATE TABLE dbo.IdentityInsertTest (Id INT IDENTITY(1,1))
GO

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