## Mar 16, 2012

### Sql query to find out missing numbers in a sequence in sql server

In sql sever by using sql query without creating any temp table we can find out missing sequence of integers from a table.

Suppose we have a table tblIndex like this :

CREATE TABLE tblIndex(
ntIndex INTEGER
)

INSERT tblIndex VALUES(1),(3),(8)

This table has following data:

 ntIndex 1 3 8

Now we want to find out missing numbers from sequence of integers from 1 to 8 i.e. output something like this:

 ntIndex 2 4 5 6 7

Solution:

SELECT ntIndex AS MissingIndex
FROM udfSequenceTable(1,8)

EXCEPT

SELECT ntIndex FROM tblIndex

For this first we have to create the table valued function udfSequenceTable which is as follow:

CREATE FUNCTION udfSequenceTable(
@MinRange INTEGER = 0,
@MaxRange INTEGER
)
RETURNS @SequenceTable TABLE(
ntIndex INTEGER
)
AS
BEGIN

;WITH cteSequenceTable
AS(
SELECT ntIndex
FROM(SELECT @MinRange AS ntIndex) ST

UNION ALL

SELECT ntIndex + 1
FROM(SELECT 1 AS ntTemp) ST
INNER JOIN cteSequenceTable ON  1 =
WHERE ntIndex  < @MaxRange
)
INSERT INTO @SequenceTable
SELECT ntIndex FROM cteSequenceTable
OPTION (MAXRECURSION 32767)

RETURN
END