Posted by: R Manimaran | July 4, 2008

Finding Missing Sequence in SQL SERVER

Finding Missing Sequence id in SQL SERVER

There may be a situation in our table the id column of the values is not in Sequence. (We may delete some id). In that case, if we want to insert some values to the tables with the id which is not present in the table. To get the no. series which is missing the following example will be useful.

Example

Set noCount on

Declare @Table1 Table(id int)

insert into @Table1 values(1)

insert into @Table1 values(4)

insert into @Table1 values(8)

insert into @Table1 values(12)

insert into @Table1 values(18)

insert into @Table1 values(25)

Select currentpoint+1 as StartIndex,nextpoint1 as EndIndex From(

Select id as currentpoint, (Select min(id) from @Table1 as B where

B.id>A.id) as nextpoint FROM @Table1 as A)As D

WHERE nextpointcurrentpoint >1

–Output

Advertisements

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

Categories

%d bloggers like this: