Gaps in SQL Server Identity Columns

By: Chad Boyd | Comments (4) | Related: > Identities

Problem
I have a table that makes use of integer based sequential values (identity based or otherwise) and I need to determine what gaps exist within this table. What is the best way to determine these gaps using a set-based approach (i.e. no cursor operations)?

Solution
A variety of simple queries can be used with any version of SQL Server to determine the first-level gaps within a given integer-based column in a table, some of which are more scalable than others. My favorite, and probably the most scalable solution I've found is a morph of a solution created by Umachandar Jayachandran.

Let's setup an example with this code and then begin to issues the queries below.

SELECT a.pkid + 1 as GapValue
FROM t1 a
WHERE NOT EXISTS(
SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1 )
AND a.pkid < (select max(pkid) from t1);

The query above will give you the minimum integer value (first-level gap) for every gap in the pkid column from table t1. The query from above will return the following result:

GapValue
-----------
4
8
14
27
31

This is a great start, however, there is at least 1 fairly problematic limitation with the above: you will never get a result set that contains all ID's in a multiple-value gap range, you will only get the first ID of a gap range (for example, notice in the data that the first gap starts at 4 as output in the result set, however there also is no 5 or 6 in the data, but the results of the query only provide the first value in a gap range). This can pose a significant problem, for example, if you are trying to insert records into the table containing the gaps based of a query from data in another table. You may still end up getting the data in successfully, but you will not have done so by using up all the gaps, or even the smallest values for the id's in question.

To address this issue, with SQL Server 2000 it would be extremely difficult and would require some iterative techniques. However, with SQL Server 2005 and the advent of Recursion and CTE's (recursive common table expressions), you can easily morph the solution from above to provide you what you're looking for, as follows:

declare @i int;

SELECT @i = MAX(pkid) FROM t1;

WITH tmp (gapId) AS (
SELECT DISTINCT a.pkid + 1
FROM t1 a
WHERE NOT EXISTS( SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1)
AND a.pkid < @i

UNION ALL

SELECT a.gapId + 1
FROM tmp a
WHERE NOT EXISTS( SELECT * FROM t1 b
WHERE b.pkid = a.gapId + 1)
AND a.gapId < @i
)
SELECT gapId
FROM tmp
ORDER BY gapId;

Not only will this result in a data-set including ALL the id values in all gap ranges, but is also extremely efficient and scalable when compared to other possible solutions using functions and iterative (i.e. cursor based) solutions:

gapId
-----------
4
5
6
8
14
15
16
17
18
19
20
21
22
23
24
25
27
31
32
33
34

Next Steps

Be sure to check out the supporting code example to understand the entire process.
This code is a prime example of new and enhanced approaches to resolving historical SQL Server problems with SQL Server 2005.
As you begin to work with SQL Server 2005, research the new coding options to expand your knowledge and improve the functionality and performance of your code.
Check out the following related tips on MSSQLTips.com:
- T-SQL
- Scripting
Special thanks to Chad Boyd from the MSSQLTips.com community for this tip

About the author

Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, June 11, 2014 - 9:10:43 PM - tj

edit for visual appeal and the gaps were removed

#"" <- for comments

[value]<- value to replace

select [columnnames #"except the id or primary key being reset"] into [createnewtable #"create a new table name"] from [datatable #"table you want to fix"]

delete from [datatable] #"table you want to fix"

DBCC CHECKIDENT('[datatable]', RESEED, [0 #"vaule you start the key at'])

insert into [datatable] select * from [createnewtable]

droptable [createnewtable]

Wednesday, June 11, 2014 - 9:04:45 PM - tj

select [columnnames"except the id or primary key being reset"] into [createnewtable "create a new table name"] from [datatable "table you want to fix"]

deletefrom [datatable "table you want to fix"]

DBCC CHECKIDENT('[datatable "again"]', RESEED, [0 "vaule you start the key at'])

insertinto [datatable] select*from [createnewtable]

droptable [createnewtable]

Friday, June 14, 2013 - 12:27:44 AM - Sandeep Kumar

create table #temp

(

ID int identity(1,1),

Name varchar(10)

)

insert into #temp(Name) Values('nikunj')

insert into #temp(Name) Values('sandeep')

insert into #temp(Name) Values('ajay')

insert into #temp(Name) Values('rohit')

insert into #temp(Name) Values('vishal')

insert into #temp(Name) Values('vipin')

insert into #temp(Name) Values('mohit')

insert into #temp(Name) Values('sumit')

insert into #temp(Name) Values('dev')

insert into #temp(Name) Values('vicky')

--select * from #temp

delete from #temp where ID in (1,4,6,9,10)

--select * from #temp

;with cte as (

select cast(IDENT_SEED('#temp') as int) as 'RowNo'

union all

select RowNo+cast(IDENT_INCR('#temp') as int) as 'RowNo'

from cte

where RowNo<cast(IDENT_CURRENT('#temp') as int)

)

select a.RowNo

from cte a

left outer join #temp b on a.RowNo=b.ID

where b.ID is null

drop table #temp

Tuesday, May 13, 2008 - 3:38:22 AM - ilies cristian

hello guys, here is another approach:

--a simple and understandable multi level gap retrieval aproach: - u create a table and populate it will all the numbers to the max of ur queryed table after that u do a left outer join and there u are:

--zzz= ur initial table; @xxx temp table used to resolve the issue:

declare @a int

declare @xxx table (id int)

SET @a=0

DECLARE @max INT

select @max = max(id) from zzz

while @a< @max

BEGIN

INSERT INTO @xxx (id)

SELECT NULL

SET @a = @a + 1

END

SET @a = 0

--update nulls to right values: 1,2,3,4,... max(id) from ur queryed table

UPDATE @xxx

SET @a = id = @a + 1

SELECT xxx.id from

@xxx xxx left outer join zzz on zzz.id = xxx.id

WHERE zzz.id IS NULL

test: lets say zzz contains foolowing ids:

        1    => ur result will be
        2
        3              4
        5              7
        6              9
        8             10
       11