Ad:

MS SQL Discussion Thread
Marked Solved
Views: 4050

Dec 8th, 2006

IDENTITY question

Expand Post »

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

Similar Threads

Do you want to be famous? in Geeks' Lounge
C command-line I/O question in C++
Context-sensitive grammar question :( in Computer Science
Welcome PC Mod Kingdom peeps! in Geeks' Lounge
Laptop LCD built into a car? in Monitors, Displays and Video Cards

plugh

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Dec 2006

Permalink

Dec 9th, 2006

Re: IDENTITY question

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.

Last edited by MCP; Dec 9th, 2006 at 10:31 am.

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

Permalink

Dec 9th, 2006

Re: IDENTITY question

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!

plugh

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Dec 2006

Permalink

Jan 5th, 2007

Re: IDENTITY question

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.

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Default current datetime for column

Next Thread in MS SQL Forum Timeline: Date Problem

DaniWeb Message

Cancel Changes

User Name
Password