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?


Given this table (and these gaps):

CREATE TABLE dbo.TableWithGaps(id int IDENTITY(1,1), x char(1));

INSERT dbo.TableWithGaps(x) SELECT TOP (50000) 'x' 
  FROM sys.all_columns AS c
  CROSS APPLY (SELECT TOP (100) * FROM sys.all_objects) AS o;
DELETE dbo.TableWithGaps WHERE id BETWEEN 11 AND 1000;     --    990
DELETE dbo.TableWithGaps WHERE id BETWEEN 10001 AND 17000; --  7,000
DELETE dbo.TableWithGaps WHERE id BETWEEN 26001 AND 44000; -- 18,000

You can identify gaps as any range where the next identity column value - LEAD(col) OVER (ORDER BY col) - is greater than 1. From there, we can identify the size of the gap by subtracting the start of the gap from the end, and order by that descending, to give us all the gaps, starting at the biggest one.

;WITH gaps AS
  SELECT id, next_id = LEAD(id, 1) OVER (ORDER BY id)
  FROM dbo.TableWithGaps
SELECT gap_start = id + 1, 
       gap_end   = next_id - 1, 
       gap_size =  1 + (next_id - 1) - (id + 1)
  FROM gaps
  WHERE next_id - id > 1
  ORDER BY gap_size DESC;


 gap_start  |  gap_end  |  gap_size
 ---------  |  -------  |  --------
     26001  |    44000  |     18000
     10001  |    17000  |      7000
        11  |     1000  |       990

Example db<>fiddle


Why would you reseed to be inside the biggest gap? Eventually (at some largely unpredictable future time), you'll reach the other side of that gap, and then failures happen. This is preventable. Just switch to bigint and stop worrying about using every last slot that exists today. One of the reasons gaps are possible in identity columns - and why SQL Server doesn't try to reuse missing values - is that this is the most efficient way to do it. Trying to identify and fill in those gaps is expensive.


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.

  • Auto-increment keys are guaranteed to be unique. This is not true for autoincrement columns, but it is a common misconception. The thing that guarantees uniqueness is a constraint (primary key or unique) or a unique index, not the IDENTITY property by itself. Sep 24 at 16:51
  • There are legal and other compliance use cases where contiguous numbers are required (like invoice numbers in some sectors), and in those cases you just have to take the hit and perform a serializable MAX+1. Sep 24 at 17:00
  • @AaronBertrand: (Learn something new today - tick!). Since too many people just lob an auto-increment number onto every table as its "Primary Key", the two are easily conflated. Mea Culpa. Totally agree on the legal use cases - auto-increment /anythings/ are not sufficient for these and custom logic /must/ be used.
    – Phill W.
    Sep 27 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.