none
SQL Server 2014 Express 64bit auto identity column value jump issue RRS feed

  • Question

  • hi all 

    Kindly advise us how to solved this issue in sql 2014 express edition 

    SQL Server 2014 Express 64bit auto identity column value jump issue

    Wednesday, August 14, 2019 6:54 PM

All replies

  • The identity is incremented during the transaction not just at the commit.  So if you insert a row and don't commit it the identity will still be incremented.  This will also happen with automatic rollbacks if you are not using explicit start transactions and commits.  For example if you try inserting a row that fails on some constraint such as a null column exception, the identity is still incremented even though the row was not inserted.
    Wednesday, August 14, 2019 7:25 PM
  • Wednesday, August 14, 2019 7:43 PM
  • SQL Server 2014 Express 64bit auto identity column value jump issue

    What for an issue? It's by design and well documented at CREATE TABLE (Transact-SQL) IDENTITY (Property) => Remarks: "Consecutive values after server restart or other failures"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, August 15, 2019 5:39 AM
  • Hi sassaqPK,

     

    As mentioned by Olaf,  SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

     

    Best regards,

    Dedmon Dai


    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

    Thursday, August 15, 2019 8:48 AM
  • Pleas advise us how to solved this issue in SQL Server 2014 express  installed on window 10 64 bit 

    Monday, August 19, 2019 6:06 AM
  • Why is this an issue for you? In common identities are used for internal ID's, never presented in a frontend.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 19, 2019 7:42 AM