Bug when recreating an empty table (repeated identity value)

Jon123Jon123 Posts: 1 New member
Scripts below can be used to recreate the problem. This concerns an SQL Compare deploy from DATABASE1 to DATABASE2.  I am forcing a rebuild of table X by having different column order. There is a row with (IDENTITY) Id 3, but all rows are deleted before deploy. After deploy the next inserted value will also be 3.The reason this happens is because DBCC CHECKIDENT(N'[dbo].[x]', RESEED, (3)) in the deploy script  works differently if no values have been inserted in the  recreated table. If the table has not been inserted to this value need to be increased by the IDENTITY increment.
------ USE DATABASE1 IF OBJECT_ID('dbo.X') IS NOT NULL DROP TABLE XCREATE TABLE X( XId INT NOT NULL IDENTITY(1, 1), a int) ALTER TABLE X ADD CONSTRAINT PK_X PRIMARY KEY CLUSTERED (XId)
 ----

-- USE DATABASE2IF OBJECT_ID('dbo.X') IS NOT NULL DROP TABLE XCREATE TABLE X( a INT, /*different column order to force a rebuild*/XId INT NOT NULL IDENTITY(1, 1)) ALTER TABLE X ADD CONSTRAINT PK_X PRIMARY KEY CLUSTERED (XId)  INSERT x(a) SELECT 1INSERT x(a) SELECT 2INSERT x(a) SELECT 3
SELECT * FROM X DELETE X /* Bug only manifests if target is empty*/

----

/* AFTER DELOY  RUN CODE BELOW IN DATABASE2 */ INSERT x(a) SELECT 4SELECT * FROM X --- Expected 4,4


Tagged:

Answers

  • Jessica RJessica R Posts: 1,159 Rose Gold 4
    Hi @Jon123 ,

    Thanks for your post!

    I am looking into this, please bear with me.

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Jessica RJessica R Posts: 1,159 Rose Gold 4
    Hi @Jon123 ,

    Thanks for your patience!

    Our developers looked into this and the reason for this behavior is a peculiarity in how SQL Server handles DBCC CHECKIDENT RESEED. As documented in https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql, if the table is empty, the increment is not added on to the reseed value for the first row inserted, but if the table is not empty, the increment *is* added on to the reseed value for the first (new) row inserted.

    I'm afraid we haven't been able to work out a way to change this that doesn't break the next identity value in other situations. :/ We originally thought we may be able to put a conditional in Compare to fix this, but we're unfortunately unable to tell the difference between a table that has never had data, and a table that used to have data but is now empty.

    With that, I'm really sorry to say that we're not able to address this at the moment unless SQL Server changes the behavior. 

    At the moment, you would need to manually edit the script. Changing 

    select @idVal = IDENT_CURRENT(N'[dbo].[X]')

    to

    select @idVal = IDENT_CURRENT(N'[dbo].[X]') + case when exists (select top 1 1 from [dbo].[RG_Recovery_1_X]) then 0 else IDENT_INCR(N'[dbo].[X]') end

    appears to work.

    I'm so sorry as I know this is not the answer you were hoping for!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.