Determine Available Blocks of Contiguous Ids

Question

We have a SQL Server table that has an int autoincrement primary key column on it.

The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 are not in use. Ids 1,000,000 through 1,100,000 are in use but ids 1,100,000 through 50,000,000 are not in use.

I am trying to figure out any and all such available range of ids. Once the ranges are determined, then we may reseed the primary key column to start at the beginning of the widest range.

Any tool or utility or SQL script out there for determining such available ranges?

Answer 1

The table's primary key space is fragmented.

As long as you're not likely to run out (I can't recall if the autoincrement values are limited to 32- or 64-bits), you should not care what the numerical values are.

Auto-increment keys are guaranteed to be unique.
Nothing more.
They are not guaranteed to be contiguous or even that they will always increase!)

The only times you need to worry about the actual, numerical values used are:

• If you have values in the table that will collide with the automatically-generated ones, or
• You're in danger of running out entirely.

Other than that, you should consider them to be meaningless (if unique) values.

As Aaron said, move to a bigint column, re-seed to accommodate the highest value you have to date and leave it at that.