Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


5
3
Sign in
to vote
Type: Bug
ID: 492452
Opened: 9/27/2009 2:47:41 AM
Access Restriction: Public
1
Workaround(s)
view
4
User(s) can reproduce this bug

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.
Details
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
Sign in to post a workaround.
Posted by MauriD on 9/27/2009 at 2:50 AM
Insert the forced IDENTITY value after having inserted an auto-generated IDENTITY value before:

TRUNCATE TABLE dbo.IdentityInsertTest
GO

INSERT INTO dbo.IdentityInsertTest DEFAULT VALUES
GO

SET IDENTITY_INSERT dbo.IdentityInsertTest ON
INSERT INTO dbo.IdentityInsertTest (Id) VALUES (0)
SET IDENTITY_INSERT dbo.IdentityInsertTest OFF
GO

INSERT INTO dbo.IdentityInsertTest DEFAULT VALUES
GO

SELECT * FROM dbo.IdentityInsertTest
go