IDENTITY value becoming too big

  • I have a table that gets a fresh set of data from our source each day. This mean I DELETE almost all the records each day and repopulate it with new data.

    I do not TRUNCATE the table because according to business rules all the records can't be deleted, so my script is:

    DELETE FROM Table1

    WHERE .....

    This deletes almost all the records each day.

    Being a DELETE, this means the primary key column, which is an IDENTITY, does not get reseeded but rather this values just keeps getting bigger and bigger.

    I am running into a scenario where the INT data type on this IDENTITY column is going to become too small.

    My ID column is on 1,500,254,112 and the maximum for an INT is 2,147,483,647.

    Now before I just go and change the data type to a bigint, I was hoping someone could give me a better solution to keep the ID value in check?

    Any suggestions?

  • Presumably your identity seed is 1? You could set it to -2,147,483,647. That would double your capacity - I don't know whether that would be enough?

    John

  • You can use DBCC CHECKINDENT to reseed identity value.

    DBCC CHECKIDENT ('Production.Product' , RESEED, 1001)

    Vishal Gajjar
    http://SqlAndMe.com

  • Presumably, the rows you're not deleting each day are the highest ID values in the set? If so, simply reseeding wouldn't help. There are obviously convoluted solutions involving deleting, updating the IDs of the existing rows back to lower IDs, then reseeding, or moving rows out and truncating/reinserting but seems like overkill.

    Bigint isn't so terrible, I'd just check whether you actually need an identity on the table at all first. Could another column serve as the PK instead (assuming you're using the identity as a PK)?

  • Hi,

    You can use DBCC CheckIdent to reseed the column, assuming the values aren't used elsewhere in the database?

    To avoid this in the future you could remove the identity property and insert with rownumber(), that way each insert will start at 1.

  • Hi,

    Yes HowardW, you are correct - reseeding won't work because of the records and their relevant ID values that remain after the DELETE.

    I was also thinking of the possibility of updating the ID values etc as suggested by you, but just did not know if all that work is really the best solution.

    Thanks for your feedback! 🙂

  • Do you have any other tables that reference the identity column on this table? For example, are there any foreign key constraints?

    John

  • Answer John's question, and then go to BIGINT. I had to do this in a system doing scans of our nodes every hour. We ran out of INTs in about 8 months.

  • Hi,

    Yes I do have tables that reference this ID Primary key column.

    So I guess I will go to BIGINT then?

    Thanks again everyone

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply