Gaps in SQL Server Identity Columns

By: Chad Boyd | Read Comments (1) | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

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.

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:

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:

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:

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
   

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code