SQL Tips by Namwar Rizvi

May 24, 2007

Finding gaps in values

Filed under: tips,TSQL,Tuning — namwar @ 7:19 PM
Sometimes you need to find gaps in values of the given integer column. For example, you need to find gaps in the id column of a given table so that you can use those values for new records to fill the gaps. Mostly it happens when we import data from some other system which does not guarantee the consecutive ids. Following is the TSQL query which returns all the gaps:

–Disable intermediate SQL Server messages
Set nocount on

–Create a table variable
Declare @m_TestTable table (id int)

—Insert some sample values
Insert into @m_TestTable values(0)
Insert into @m_TestTable values(5)
Insert into @m_TestTable values(6)
Insert into @m_TestTable values(7)
Insert into @m_TestTable values(10)
Insert into @m_TestTable values(15)

—Run actual query to display gaps
Select currentPoint+1 as StartIndex,nextPoint1 as EndIndex
From
( –Create pairs of start and end points
Select id as currentPoint,
(
Select Min(id) from @m_TestTable as B
Where B.id>A.Id
) as nextPoint
From @m_TestTable as A

) as D
–Removing all incorrect pairs where Start is greater than End
Where nextPointcurrentPoint>1

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: