November 28, 2018 at 11:28 pm
In a development SQL database I’m using I had the problem that my primary key columns were overflowing. The primary key columns are integer generated by a SQL IDENTITY. Due to various development activities like cloning records I was running out of space for my primary keys. There were however a lot of holes between the primary keys. Please help us to remove the gaps between identity values so that we use them.
November 29, 2018 at 6:19 am
There's no easy way to get that done, at all. Instead, you could reseed the identity to start at the max negative value and then let it increment naturally from there. That will double the length of time until you run out of space. Also, you can change the data type to bigint as well as setting the starting point to a negative value. You will need to monitor it though. If you're using so many identity values as you're literally filling up the INT data type, you could get back to zero again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply