locked
Identity values jumps by 1000 after restarting sql server. RRS feed

  • Question

  • Hello,

    We just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns

    "When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).

    "For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."

    After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept

    1. Use sequence object -

    It is not possible for me because

                               a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.

          b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.

    2. Use Trace Flag 272 (-T272)

           I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.

    I don't want to do any changes in my database structure.

    1.So plz suggest me any solution for it

    2. Can any one suggest me that how to use this -T272 or why it is not working.

    Friday, January 9, 2015 8:23 AM

Answers

  • I am having one big application having 2000+ tables and 2000+ procedures.It was developed in sql server 2008R2, when identity column means increment of column values by 1 (here increment value is 1),restarting or any faliour won't disturb its functionality.

    but in sql server 2012 when ever i restart Sql server it increased the seed value of identity column by 1000.I don't want that huge amount of gap and i don't want to use/IDENTITY_INSERT ON it is not a good practice/.I can't change in my procedures and tables because number of objects are more than 2000.

    Look, there is no difference between SQL 2008 R2 and SQL 2012. In both IDENTITY can give gaps. It just so happens that they are a lot more visible in SQL 2012.

    If your business requirement is that the ids should be consecutive, you made an incorrect design decision, and you need to change your code. Bad design decisions can be costly to repair.

    However, you should ask yourself whether you need all these 2000 tables really need consecutitve IDs - I sincerely doubt that they do.

    And by the way, 2000 tables with IDENTITY sounds a wee bit too many to me, but that's another story.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 9, 2015 2:58 PM

All replies

  • I would say you should not worry about the jump/gap. This is known issue.

    Ref: http://msdn.microsoft.com/en-us/library/ms186775.aspx -Check the remarks.

    If your column is a bigint datatype, then you are safe enough as it can serve for long time. Read the below link for your reference.

    http://sqlmag.com/sql-server/determining-identity-value-capacity

    Regarding Sequence - SEQUENCE as well has the same issue, may be less prone. 

    Regarding T272 - You may have a slight performance issue due to enablement.

    • Proposed as answer by Olaf HelperMVP Friday, January 9, 2015 8:57 AM
    • Unproposed as answer by Alok c Shahi Friday, January 9, 2015 12:19 PM
    Friday, January 9, 2015 8:36 AM
  • The solution is simple: just roll over and fall asleep again. This is nothing you should bother about.

    The IDENTITY is meaningless number, and it is designed to create gaps. That is, if you insert a row and later roll back, that number is lost. And it is that way to improve concurrency.

    If you have a business requirement that numbers must be contiguous, you should never have used IDENTITY in the first place.

    And of course, you should not use TF272 - that is a "go slower" button.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Olaf HelperMVP Friday, January 9, 2015 8:57 AM
    • Unproposed as answer by Alok c Shahi Friday, January 9, 2015 12:19 PM
    Friday, January 9, 2015 8:41 AM
  • In any case it will never change the numerical order in which your numbers get generated so you would still be fine with having the gaps in the sequence.

    At any time if you want to get data ordered and return Top x, Bottom x etc you can generate continuos sequence number on the fly in query using ROW_NUMBER() function.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 9, 2015 8:55 AM
  • Latheesh NK

    Actually most of the tables are having int identity columns and it can have huge transaction.What if i don't want this gap/jump.

    Can't i suppress this feature? I think it should behave like previous version because no one wants to re develop already existing database again.

    My main concern is to get similar feature as Sql server 2008 in sql server 2012,but how?

    Friday, January 9, 2015 11:23 AM

  • Actually most of the tables are having int identity columns and it can have huge transaction.What if i don't want this gap/jump.

    Its known issue and you can refer the below link and REMARK Session(I gave the link in my previous post, but it looks like you could not read )

    http://msdn.microsoft.com/en-us/library/ms186775(v=sql.90).aspx

    However, to make sure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

    If you are reusing a removed identity value, use the sample code in Example B to look for the next available identity value. Replace tablenamecolumn_type, andMAX(column_type) - 1 with a table name, identity column data type, and numeric value of the maximum allowed value (for that data type) -1.

    Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column.

    If a table with an identity column is published for replication, the identity column must be managed in a way that is appropriate for the type of replication used. For more information, see Replicating Identity Columns.

    Can't i suppress this feature? I think it should behave like previous version because no one wants to re develop already existing database again.

    My main concern is to get similar feature as Sql server 2008 in sql server 2012,but how?


    Which version you are talking about? As far as I am aware, the issue present in all versions. May be you would not have noticed it. (You can look at the link added, its 2005 version only).
    Friday, January 9, 2015 11:39 AM
  • Erland Sommarskog,

    1. Initially i want to know that how to use TF-272,because it is not working here.Can you tell me process of it.

    2. Might be it is slow,i want to check how much slow it is.It is better to get consecutive number rather than such huge gaps.

    Friday, January 9, 2015 12:04 PM
  • Latheesh NK

    I think you are not getting my problem.

    I am having one big application having 2000+ tables and 2000+ procedures.It was developed in sql server 2008R2, when identity column means increment of column values by 1 (here increment value is 1),restarting or any faliour won't disturb its functionality.

    but in sql server 2012 when ever i restart Sql server it increased the seed value of identity column by 1000.I don't want that huge amount of gap and i don't want to use IDENTITY_INSERT ON it is not a good practice.I can't change in my procedures and tables because number of objects are more than 2000.

    I got one solution in which i have to set only one startup parameter,which is not working.If it will start working then there is need not to do any changes in existing application.

    what you are suggesting is to do changes in already existing application which is time taking and risking task.

    Can you tell me how to use -T272 or why it is not working.

    Friday, January 9, 2015 12:13 PM
  • Erland Sommarskog,

    1. Initially i want to know that how to use TF-272,because it is not working here.Can you tell me process of it.

    2. Might be it is slow,i want to check how much slow it is.It is better to get consecutive number rather than such huge gaps.

    Did you restart the server once the -T272 is set?

    Check the below link:(stepwise)

    http://www.dfarber.com/computer-consulting-blog/2014/2/13/how-to-solve-identity-problem-in-sql-2012.aspx

    Friday, January 9, 2015 12:16 PM
  • Yes i did the same but still it is not working.
    Friday, January 9, 2015 12:17 PM

  • I am having one big application having 2000+ tables and 2000+ procedures.It was developed in sql server 2008R2, when identity column means increment of column values by 1 (here increment value is 1),restarting or any faliour won't disturb its functionality.


    I would say its just working or by chance (there is no guarantee), but you can expect the GAP in SQL 2008 R2 as well. :)
    Friday, January 9, 2015 12:20 PM
  • I can expect but restarting the server is will never give me gap.

    If any transaction is rollbacked then only that value is missed. and gap between is not that much huge.

    Still problem is same what to do?

    Friday, January 9, 2015 12:37 PM
  • I don't want that huge amount of gap


    That's huge for you? If you use integer as data type you could restart your SQL Server 1 million times and you would still have place for 1 billion of records.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 9, 2015 12:52 PM
  • Identity fields are unique, not sequential.  They can never be guaranteed to be sequential without gaps.

    Friday, January 9, 2015 2:00 PM
    Moderator
  • I must say the 1000/10000 jumps are annoying to us - developers/DBAs - who routinely see the row identifiers which should not be exposed to end users.

    Currently only the SELECT @NextID = max(ID)+1 .... method ensures contiguous identifier sequence.

    Connect: https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Friday, January 9, 2015 2:01 PM
    Moderator
  • Identity columns are not UNIQUE by default.

    We need to specify CONSTRAINTS to make the column UNIQUE.

    Create Table Test_IdentityUnique(Id int identity(1,1), Name varchar(50))
    
    Insert Test_IdentityUnique (Name) Values('Test')
    
    SET IDENTITY_INSERT Test_IdentityUnique ON
    
    Insert Test_IdentityUnique (Id,Name) Values(1,'Test')
    
    SET IDENTITY_INSERT Test_IdentityUnique OFF
    
    Select * From Test_IdentityUnique
    
    Drop table Test_IdentityUnique
    

    Friday, January 9, 2015 2:08 PM
  • I am having one big application having 2000+ tables and 2000+ procedures.It was developed in sql server 2008R2, when identity column means increment of column values by 1 (here increment value is 1),restarting or any faliour won't disturb its functionality.

    but in sql server 2012 when ever i restart Sql server it increased the seed value of identity column by 1000.I don't want that huge amount of gap and i don't want to use/IDENTITY_INSERT ON it is not a good practice/.I can't change in my procedures and tables because number of objects are more than 2000.

    Look, there is no difference between SQL 2008 R2 and SQL 2012. In both IDENTITY can give gaps. It just so happens that they are a lot more visible in SQL 2012.

    If your business requirement is that the ids should be consecutive, you made an incorrect design decision, and you need to change your code. Bad design decisions can be costly to repair.

    However, you should ask yourself whether you need all these 2000 tables really need consecutitve IDs - I sincerely doubt that they do.

    And by the way, 2000 tables with IDENTITY sounds a wee bit too many to me, but that's another story.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 9, 2015 2:58 PM
  • I think he is not aware of the problem you are talking about, and it's accepted to have gaps in the Identity Column but to have gap of 1000 each time we restart the server is something very annoying. and my be he is right about one thing that we did design mistake to use MSSQL from the beginning. as they don't have any solution for a very small issue like this, and keep replying like answer machine, I had the same problem without any solution. 
    • Edited by maga9001 Wednesday, December 27, 2017 9:47 AM
    Wednesday, December 27, 2017 9:46 AM
  • Make sure you have the latest Service Pack installed.

    MS changed the code to not jump when the SQL Server cleanly shuts down.  You should only see large gaps when the server crashes or restarted unexpectedly.

    https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version--edition-and-update-level-of-sql-server-a

    Wednesday, December 27, 2017 12:04 PM
    Moderator
  • The Microsoft has made a huge mistake, and their "MVPs" did not have humility in this forum to recognize the problem. They was trying to explain the inexplicable and unacceptable.

    Could you imagine how much system around the world are using IDENTITY and this feature changes it's behavior without compromise?

    While SQL Server developers team was trying to fix the BUG, it seems that the MVPs tried to persuade us that IDENTITY has the same purpose of GUID columns.

    In fact, it's a BUG from SQL Server Cache. They did not fix it yet, but at least they gave us the option to disable the cache and it's BUG in SQL Server 2017. And now you can have legacy database and new databases at the same server.

    IDENTITY_CACHE = { ON | OFF }

    Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

    (...)

    G. Set IDENTITY_CACHE

    This example disables the identity cache.

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;
    Friday, February 2, 2018 1:21 PM
  • Now, it is an "incorrect design decision"?
    While I was migrate databases from Oracle to SQL Server 2000, the Microsoft has recommended using IDENTITY to replace ORACLE SEQUENCE.

    "you need all these 2000 tables really need consecutitve IDs"
    Do you know how many tables he have? Do you know how many tables are in my databases? And around the world?


    "I sincerely doubt that they do"
    It's a joke or you really know his business requirements?

    The Microsoft has made a huge mistake, and their "MVPs" did not have humility in this forum to recognize the problem. They was trying to explain the inexplicable and unacceptable.

    Could you imagine how much system around the world are using IDENTITY and this feature changes it's behavior without compromise?

    While SQL Server developers team was trying to fix the BUG, it seems that the MVPs tried to persuade us that IDENTITY has the same purpose of GUID columns.

    In fact, it's a BUG from SQL Server Cache. They did not fix it yet, but at least they gave us the option to disable the cache and it's BUG in SQL Server 2017. And now you can have legacy database and new databases at the same server.

    IDENTITY_CACHE = { ON | OFF }

    Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

    (...)

    G. Set IDENTITY_CACHE

    This example disables the identity cache.

    ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

    Friday, February 2, 2018 1:36 PM
  • In fact, it's a BUG from SQL Server Cache. They did not fix it yet, but at least they gave us the option to disable the cache and it's BUG in SQL Server 2017. And now you can have legacy database and new databases at the same server.

    It's a BUG in the application to assume there will be no gaps in IDENTITY. SQL Server has always had gaps since IDENTITY was introduced. I do believe, however, that Microsoft erred in making the identity caching too aggressive in later versions in the interest of performance. The configurable option is a good compromise for those that don't need high performance but developers should be aware disabling identity cache will not eliminate gaps.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 2, 2018 1:53 PM
  • Good day Elton,

    In one message you successfully did two HUGE issues

    1. Responding an old thread from two years ago, which was closed already by the OP that got his answer, is usually consider as abusing in forums. A forum is not a documentation which we need to keep updated. Once a thread was close, especially if the closer was the OP, and after awhile, the thread considered as close and you should not open it. 

    This is not your thread!
    please don't hijack it :-)

    If you want to open a discussion or you have question, then you can and should open a new thread under your name. In the new thread you can add link to relevant old thread like this one. 

    2. A BUG by definition is unplanned while the behavior of IDENTITY is by design in order to improve performance.

    You should really learn about how SQL Server works behind the scene and why, including taking part in the internal discussion with Microsoft developing team (well if you have this privilege) before you blame someone else (who do have this privilege) that he make mistake.

    In fact there is no BUG and there was no fixing to any bug, but simply improving and evolution of application (SQL Server) with new features and adding a new feature, which gives us more control in simpler way. This is a continues of a feature added in 2016. "Database-scoped configurations introduced in SQL Server 2016 has luckily been extended in SQL Server 2017 to include IDENTITY_CACHE option" (***) , and other database scoped configurations.

    Even using "SET IDENTITY_CACHE=OFF" will NOT ensure that you will not have gaps! "Some other causes relate to batch INSERT statement failures that, despite the transaction being rolled back, the allocated identity value is never rolled back thus causing a gap" (Please read more). Therefore, in most cases why should we SET IDENTITY_CACHE to OFF ?!? There is a reason the default is ON. Performance is a huge important to professional work! If you do not know how to deal with some behavior then you can always get (hire) someone else who do know.

    Maybe the issue is in the application design that expect getting IDENTITY without gaps ?!?

    Think about this please!

    If needed you might asked your developers to learn a bit more about the resources that they use (SQL Server in this case), before they design the application. I am sure that this issue cannot be done by professional architect and I assume that these developers design the application themselves.

    * There are cases that using the parameter OFF can be useful but it is not recommended in most cases.

    >> it seems that the MVPs tried to persuade us that IDENTITY has the same purpose of GUID columns.

    By definition IDENTITY is a surrogate key and for the sake of using it as primary key it can be considered as something that can be used in cases that GUID is used and vise versa (since these are not the same. each case should use the best option for that specific case, but both are used as surrogate keys)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    Friday, February 2, 2018 2:27 PM
    Moderator
  • As a customer, that represents and influences large customers to adopt Microsoft solutions, it would be best to hear from Microsoft a humble response.

    I love SQL Server and it's features . . . and now SQL Server 2017 has IDENTITY_CACHE=OFF;

    ISSUES:

    1. "Responding an old thread...which was closed."
    * Why not disable the "Reply" option of closed threads?

    ;-)

    2. Yes! UNPLANNED and irresponsibility with customers who bet on previous versions of SQL Server.

    Why not use insert STATISTICS to determine the size of the IDENTITY cache for each table?

    If it's cached, why not use a ROLLBACK after restart and/or check the last code generated by IDENTITY?

    At least, Microsoft erred in making the identity caching too aggressive, sometimes to tables that has few inserts in a whole year.

    "Ok! We didn't think about Database on cloud, attached on a server that the developer had no control over the settings."
    "Ok! The earlier SQL Server versions had no SEQUENCE and we advised our costumers to use identity columns instead."

    Instead, it's response something like:
    -"You're wrong to walk with rolex on your arm. You will be stolen on the street."
    The rolex owner is wrong and not the thief.

    Inversion of guilt. That is the kind of arrogance response that I was talking about.

    "improving and evolution of application (SQL Server) with new features". . . and then add back to the old option (IDENTITY with no cache) . . . in order to REPAIR the UNPLANNED.

    Friday, February 2, 2018 9:18 PM
  • At first I was confused by this "feature" but now I see if as very handy to determine when the last record in a table was written before a sql restart.  I find this handy when trying to resolve any data issues
    Wednesday, September 26, 2018 4:28 PM
  • An identity column is a SURROGATE key which should not be presented to the end user. The surrogate key is to be used by the database only.

    I can't agree with this statement :-)

    A surrogate key is a unique identifier which is not derived from application data/information. Meaning (in my words) it does not have a value with meaning like a person's name and it does not derived from other data like a combination of UserID (which can be surrogate key) together with ActivityID (which can also be surrogate key).

    A surrogate key is definitely can be presented to the end user and even used directly by the end user. A good example is your Azure subscription ID, which you must use if you work with PowerShell for example. In most systems a UserId is surrogate key which is used in the application side and presented to the user in one way or another (for example in the URL when you go to the user page in some applications).

    I think that you meant to say that the value of surrogate key is meaningless

    As you said, gaps has no meaning in surrogate key, since the value of surrogate key has no meaning by definition :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, September 27, 2018 9:05 AM
    Moderator
  • Gents this is old thread please encourage users not to post on old thread and ask them to create new one. There is no restriction on that

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, September 27, 2018 9:12 AM