Value of identity-based primary column reaches max

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Value of identity-based primary column reaches max

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-12-09 : 09:26:55

Hi,
What if value of numeric-identity-based column reaches max?
In details, for example, I have a table and choose a primary column called “RowID” as INTEGER and its values are provided using identity (1,1) when the table is created. Day by day, rows of the table keep growing and when the value of primary column reaches MAXIMUM value of 2,147,483,647 or 2^31-1, I cannot insert more rows afterwards in the table because no more value available or > 2,147,483,647 can be assigned for RowID column???
Thanks
johnsql

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-09 : 09:42:36

use bigint for the identity.

if your table gets big enough to overflow the biggest signed int64, you'll be sure to win some sort of prize from MS.

http://www.elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-09 : 13:53:06

When you use an IDENTITY column, you need to chose a data type that will last for the expected design life of the system without exceeding the maximum size of the data type.

The following query shows the maximum average rows per day that you can insert in an IDENTITY column of type INTEGER for system design lives of 1 through 100 years. An INTEGER would allow you to insert 588,351 rows/day for 10 years, 196,117 rows/day for 30 years, and 58,835 rows/day for 100 years.


select
	[Design Years] = a.number ,
	[Design Max Rows/Day] =
		floor( 2147483647. / ( 365 * a.number) )
from
	-- Number table function available
	-- from Script Library forum
	F_TABLE_NUMBER_RANGE(1,100) a

Results:


Design Years Design Max Rows/Day     
------------ ----------------------- 
1            5883516
2            2941758
3            1961172
4            1470879
5            1176703
6            980586
7            840502
8            735439
9            653724
10           588351
11           534865
12           490293
13           452578
14           420251
15           392234
16           367719
17           346089
18           326862
19           309658
20           294175
21           280167
22           267432
23           255805
24           245146
25           235340
26           226289
27           217908
28           210125
29           202879
30           196117
...
...
100          58835

(100 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-12-10 : 01:11:26

"when the value of primary column reaches MAXIMUM value of 2,147,483,647 or 2^31-1, I cannot insert more rows "

Ah, but you can insert more rows! Identity will just go negative and start flowing back towards 0. Double your money!

Kristen

Kristen
Test

22859 Posts

Posted - 2006-12-10 : 01:12:26

Assuming you have been deleting some rows (2,147M rows is quite a big table!!) will SQL Server just carry on from the low numbers again [even though there are higher numbers stored]?

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-11 : 07:20:28

quick and dirty

create table bar (duh tinyint identity(1,1) , bah char(1))

set nocount on
declare @x int

set @x = 1

while @x < 260

begin

insert into bar(bah) values ('f')

set @x = @x +1

end

select * from bar

gives you a nice

Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.

It stopped inserting after 255

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

johnsql
Posting Yak Master

161 Posts

Posted - 2007-01-10 : 15:04:02

DonAtWork,
All in all, if the type of my identity column is INT, then after the max value for that identity column for a row has reached, then I can NOT insert more rows for the table because the overflow error occurs. So, "Ah, but you can insert more rows! Identity will just go negative and start flowing back towards 0." can not happen at all!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-10 : 17:49:01

quote:
Originally posted by Kristen

Ah, but you can insert more rows! Identity will just go negative and start flowing back towards 0. Double your money!

Kristen

quote:
Originally posted by Kristen

Assuming you have been deleting some rows (2,147M rows is quite a big table!!) will SQL Server just carry on from the low numbers again [even though there are higher numbers stored]?

Kristen

You can't possibly be serious on either of these??

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-01-10 : 18:26:31

I was on the first because I thought that was what happened. I was thinking about setting the INCREMENT differently on IDENTITY columns on separate servers so that the rows were unique and could be merged - e.g. Even numbers on one, Odd numbers on another, Negative numbers on a third etc) So one could set the seed to a large negative number and have it increment up to Zero and then it would turn positive. Can't see that is much use in most instances though.

If you are deleting old, stale, (low numbered) record as the IDENTITY grows - i.e. so you are left with just high numbered IDENTITYs can you use DBCC CHECKIDENT to reset the Identity to, say, 1 and let it increment towards the high number? - the plan would be that the remaining high numbers will have been deleted (because "stale") before duplicates occur

Either way, I still think that 2,147M rows is an unfeasibly big table

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-10 : 23:01:15

quote:
Originally posted by Kristen

Either way, I still think that 2,147M rows is an unfeasibly big table

You'd think so, but just the other day I fixed a customer reported bug in an app of mine - it broke because I was using an int to store the row count in his table, which overflowed because he had more than 2b rows!

enormous!

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-01-11 : 03:30:50

Boggling! I still can't see what use 2b rows in a single table it.

Telephone number list maybe?!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-11 : 10:04:08

I didn't ask him what it was for, and he didn't offer. I find people are usually pretty protective of their schemas

From the name of the table it wasn't obvious what it was storing.

EDIT: The biggest table I have ever dealt with in sql server was 900m rows, about halfway there. i used a bigint for the pk.

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-01-12 : 01:03:15

So ... if table contains IDENTITY column values from, say, 2,147,000,000 to 2,147,483,647 (the max) can I reset the next value back to 1 with DBCC CHECKIDENT and start filling up the table from 1 to 2,146,999,999 ?

Hmmm ... BoL says:

"If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table."

so what to do in these circumstances? Renumber the existing rows "lower", and reseed to the new max value?

I was hoping to just start again at 1 and by the time the number had got higher the high numbered rows would have been purged

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-12 : 02:44:19

Yes you can refill the lower numbers, just make sure you don't collide.

We actually had to do this for two of our systems. We expected them to merge at some point, so we started one of the tables at 10,000. The other system was allowed to started incrementing at 1. 10,000 was used due to the number of rows expected (very small). We didn't end up merging, but it gave us an easy way to do it if needed.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources