January 16, 2016 at 6:03 am
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.
January 16, 2016 at 6:51 am
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).
January 16, 2016 at 7:22 am
Thank you for that usefull information :hehe:
January 16, 2016 at 10:05 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy