IDENTITY question

[plugh]

Hi all - I am a newbie to this forum, but maybe someone there can help.

I have a table using an Identity property. The purpose of it is to just have a unique id value... the problem is that the system automatically fills this table with lots of data, over and over. Certain records get removed and then regenerated. This is fine, but I worry that at some point the Identity value will hit the max (bigint) and then what will happen? I cannot clear the table and start over at any point, and it's not clear if resetting the seed to 0 or 1 will work properly when it is incrementing and finds an existing value. Docs seem to tell you not to reset seed to value below existing value.

Does anyone know what will happen once the identity field hits it's max value? Can I reseed the identity to start over at 1 and fill in the gaps?

Thanks
-Dave

[MCP]

Valid theoretical question (I think it will error with overflow), but do you practically think you'll get there? bigint can go up to 9,223,372,036,854,775,807

That's pretty big... Just to try and put that into perspective, it would take ~292 years to break the bigint limit if you were to insert 1,000,000,000 (a billion!) rows per second continually.

[plugh]

Very good insight on the improbability of ever reaching the limit of bigint.... I guess I have *some* time to figure out a way to recreate and reseed the data file once it starts getting up too high! I think that if the coding logic will last 20 yrs, that should prob be good enough for me not to worry about it now!

Thanks!

[campkev]

from a practical standpoint, I had this happen on a database with an int identity column. You will get an arithmetic overflow exception. Since we are purging and only keep 60-70 million rows at a time, reseeded to 0 started over. Be careful because you will run into problems if you are not purging properly and new identity hits on an existing value.