March 22, 2004 at 9:00 am
I have a question about what happens when you reach your limit on Identity fields?
I have a table that is used as a temporary storage area for reporting. Because of various reasons, I pull a lot of data together and put it into a table that is then used to create a report. Every time a user runs a report, their old data is deleted and new data for that user is put into the table. Each user's data is identifiable because I use their User ID as one of the fields in the table.
In this table, I have created a key field that is an identity field. Right now this field is an integer. I ran a test the other day inserting records into a table that had a tinyint identity field to see what happens when I reached a number of records that exceeded my identity field's type limit. When the limit is reached, I get an error.
I was wondering if there is a way to let SQL know to reuse, or start over, on the numbering when the limit is reached?
Also, in case someone wants to point it out, I realized while writing this post that I should just put a sequence number with the user id and this problem won't occur. I am going to make this change but I still have the question about the identity field.
Thanks.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
March 22, 2004 at 10:22 am
Some ruminations on this topic.
1st, there is an ability to "reseed" the identity value DBCC CHECKIDENT (refer to BOL), however I perceive that your intent is to "reseed", when needed, as your system executes. I suppose one could add logic to an insert trigger that would accomplish this task. I don't know that I would be comfortable with this approach.
However, it seems to me that since this approach (identity) is not problem free, then the design may need to be reconsidered. It sounds to me like you are not really using the "identity" value.
While uniqueid takes more storage space than the approach you are currently using, you might want to consider using it. You will never run out of unique ids. Also since you don't let your table grow forever, maybe the disk space impact will be limited in time and duration. You could still retain use of the personal Id of each individual.
GaryA
March 22, 2004 at 12:44 pm
Depending on record size you might want to consider using a temp table for these to ensure less likely to run into especially if the data isn't very large.
As for when you reach the limit, yes it will not be able to process so you have to create a method to reuse the empty spots. As stated reseed is best for an IDENTITY column or pick a time when all the data can be deleted and do a truncate on the table. This will reseed automatically.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply