none
Identity Columns Randomly Regress to a Lower Value RRS feed

  • Question

  • Hello,

    We have an in-house developed database application that uses SQL Server 2016 (13.0.5101.9) for backend data storage.  Numerous tables have identity columns configured as the key field.  This week two of these identity columns randomly changed to substantially lower numbers. 

    In one case the identity column changed from 20200954 to 20160296.  In the other case the identity column changed from 20200000 to 20120046.  In both cases the identity column was configured as an INT value with an increment step of 1. 

    We had previously encountered the better known issue where identity values can skip by 1000 during a server restart.  We took the prescribed measures to eliminate this.

    Anyhow, in approximately ten years of use of this application (using two or three versions of SQL Server over this time), I don’t ever recall experiencing identity values randomly regressing to a lower value.  I am wondering…

    1.)    If anyone else has experienced this?

    2.)    Is this a known issue?

    3.)    Is there a known remedy?

    It’s very unsettling having identity values randomly change.

    Thank You,

    Dale

    Friday, October 4, 2019 1:18 PM

All replies

  • Please post the results of SELECT @@VERSION.

    I have never in ~20 years of SQL Server experience seen an identity field change to a lower number. 

    The only way that can happen is: the database was restored or someone ran DBCC CHECKIDENT with RESEED and set it lower.  RESEED does not do any checking if you set it to a lower value than currently in use and can cause duplicates.

    Friday, October 4, 2019 1:38 PM
    Moderator
  • Tom,

    Thank you for the response.  Yes, I think I too can say in 20 to 25 years of experience with SQL Server I have never seen an identity column change to a lower number.

    Below are the results from the SELECT @@VERSION query.

    Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

    The database did not go through a restore.

    As the author of the application (vb.net & ado.net) in question, I have a form where I can change certain identity values at the beginning of a new fiscal year.  However these changes, which employ a function which calls DBCC CHECKIDENT, always change these identity fields to a value such as 20170000, 20180000, 2019000, etc., depending on the fiscal year.  As you stated, the only way the changes that occurred could have happened would be for someone to have executed a DBCC CHECKIDENT with these rather random RESEED values.  Only me and my coworker have this level of access to this SQL Server instance.

    So, I have no plausible explanation for these two identity values changing.  For what it’s worth, I took a look at the status of the drives comprising the RAID 10 array on which this SQL Server instance lives, and there were no issues.

    Thank You,

    Dale

    Friday, October 4, 2019 2:29 PM
  • Since your application has a way to reseed, my initial guess is someone ran it from the app.

    You should not be relying in an identity field for "business logic".  It looks like you are using it for some kind of yearly transactions.

    There really is no reason to reseed an identity field unless you are running out of numbers.  Your app should not need to do that.  

    Friday, October 4, 2019 2:37 PM
    Moderator
  • Tom,

    Well, it looks like for now, this will remain somewhat of a mystery. 

    The application’s reseed functionality is programmatically designed to only reseed to an even xxxx0000 value, so the values that suddenly appeared (20160296 and 20120046) would have been impossible via this method.  And this particular module of the app is access limited.

    If I discover anything further in the following days I will follow up.

    Thank You,

    Dale

    Friday, October 4, 2019 3:47 PM
  • Is it possible it was reset to 20120000 and then 45 items were inserted, which would make the current value 20120046?
    Friday, October 4, 2019 5:12 PM
    Moderator
  • Tom,

    The application module that uses the identity column that suddenly changed to 20120046 has had no records generated since I reset the counter to 20200000 on July 1st.

    The other application module, the one that suddenly changed to 20160296 was also reset to 2020000 on July 1st.  In this case, during the intervening months, records were generated which brought the counter up to 20200954.  Then, earlier this week, our data entry operator entered a batch of records, the first of which was assigned number 20160111 by the identity column.  The operator didn't notice the records were being assigned 2016xxxx numbers instead of 2020xxxx numbers until she got to record 20160296.

    Dale

    Friday, October 4, 2019 5:26 PM
  • Yes, I have experienced IDENTITY value going backwards. However, this was under SQL 6.5 and that was very long ago.

    No, I agree with Tom, this is someone who has run DBCC CHECKIDENT for some strange reason.

    And I have the same sentiment as Tom against this arrangement. The main reason to use IDENTITY is to permit high-concurrency inserts. It appears that you are expecting at most 9999 rows per year, so that is not an issue for you. So this should not be an IDENTITY column in the first place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 4, 2019 9:26 PM
  • You would be better off using a persisted calculated column  of (YEAR(transaction_date) * 1000000) + identity field.  Then you get the best of both worlds.  You have an increasing identity field for inserts and a column for sorting transactions by year and never need to reseed the identity value.



    Monday, October 7, 2019 11:56 AM
    Moderator
  • Based on your description what happened was: someone reseeded the table to 20120000, tried to insert 45 records, which failed due to PK violation (assuming you have a PK on the identity field), increasing the identity next value to 20120046.

    Monday, October 7, 2019 12:50 PM
    Moderator
  • Tom,

    To be a bit more specific, the two tables that experienced the Identity column changes are named TIME_MX and IAR_MX.  At the end of fiscal year I personally reset the identity counters on these two tables to 20200000.  I refer to the *_MX tables as transactions tables since records entered into these tables are posted against other tables and then, after posting, are archived into corresponding archive tables named TIME_MA and IAR_MA respectively.  The archive process then removes the archived records from the transaction table.

    When the user, last week, went to archive records from TIME_MX into TIME_MA, there are several programmed checks that occur prior to executing the query that moves records from the transaction to the archive table.  Once of these is a check to see that there are  no records already in the archive table that have the same identity column value as those in the transaction table.  This is what brought the identity column issue to our attention.  The range of records that the operator created last week (without noticing the 2016xxxx identity value on the data entry form) already existed in the archive table from several years ago.

    It was the identity column for TIME_MX that unexpectedly flipped from its last good value, 20200954, to 20160111.

    The other table in question, IAR_MX, has had only five records entered into it and archived since the start of the new fiscal year.  In theory, its identity column should be sitting at 20200005, but when reviewing the problem pertaining to TIME_MX I noticed that IAR_MX’s identity column had also taken on a random value, this being  20120046.

    This application has been in service for about 12 years.  Knowing the users who have access to it, their knowledge level, and the degree of access they are afforded through the application’s security controls, I do not believe any would have or could have intentionally or inadvertently run a DBCC CHECKIDENT command.  In my mind, the cause of this remains an open question.

    If I should discover or uncover any further clues I will follow up. 

    Thank You,

    Dale

    Monday, October 7, 2019 1:36 PM
  • any update?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 9, 2019 8:30 AM
  • Sorry - No further info at this point.

    Thank You,

    Dale

    Thursday, October 17, 2019 4:36 PM