Auto Increment INT, overflow or limit exceeds - possible??

• Hinti

  SSC Enthusiast

  Points: 191

  More actions

  January 16, 2016 at 6:03 am

  #303767

  Hello,

  I know, with INT there is a looooot of numbers available.

  But my table is a working table. So this table often has rows which will be deleted a bit later.

  I mean, actual I have no rows inside, but the Autoincrementor not Begins at 1. So in the meanwhile the autoincement increments with an INT higher then thousand, But it's maybe one/first row only!

  Is there a risk that at anytime in the future, the INT Limit was reached? and what's then?

  what is when I ever insert the 'first' row again, and it's the first overflown increment of INT, but the table contains just some rows?

  - does the Autoincrement begin at the first free number? - or does an error rise then?

  Best Regards, Hinti.

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  January 16, 2016 at 6:51 am

  #1852367

  With int, the maximum value supported by SQL Server is 2,147,483,647. Since you are currently at 1,000, I think you do not need to worry just yet.

  If you do run out of values, then if you use IDENTITY you will get a runtime error. It will not automatically cycle back. If yiou use a SEQUENCE instead of IDENTITY, then you can specify that is has to cycle. But you will need to make code changes in order to replace IDENTITY with SEQUENCE.

  Probably a better option for you, especially because you already say that the table is a working table that is normally empty, is to periodically reseed the identity column. Look up DBCC CHECKIDENT in Books Online, and pay special atttention to the RESEED option.

  Just for completeness, I will also add that yet another option is to change the data type of the column to bigint (maximum value 9,223,372,036,854,775,807 - this will allow you to insert a million rows per second, for almost 300,000 years in a row).

  Or if you fear that you will overflow even that, you can consider using decimal(38,0) - the maximum here is a number consisting of 38 9's (which will allow you to maintain that same pace for a whopping 31,709,791,983,764,586,504,312,531 years).

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• Hinti

  SSC Enthusiast

  Points: 191

  More actions

  January 16, 2016 at 7:22 am

  #1852369

  Thank you for that usefull information :hehe:

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 16, 2016 at 10:05 am

  #1852384

  I had that question in a class I was teaching last week. Even with an Integer, you can insert 1 row a second, every second, for 81 years before you'll run out of identity values.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass