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,nextpoint-1 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 nextpoint-currentpoint >1

–Output