IDENTITY value becoming too big

Question

IDENTITY value becoming too big

Casper101

Hall of Fame

Points: 3903
More actions
March 22, 2013 at 5:59 am

#291412

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?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 2

You can use DBCC CHECKINDENT to reseed identity value.

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

Vishal Gajjar
http://SqlAndMe.com

HowardW One Orange Chip Points: 29109 More actions · Answer 3

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)?

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

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.

Casper101 Hall of Fame Points: 3903 More actions · Answer 5

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! 🙂

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

John

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

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.

Casper101 Hall of Fame Points: 3903 More actions · Answer 8

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