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 = 1 

         WHERE ntIndex  < @MaxRange

    )

    INSERT INTO @SequenceTable

    SELECT ntIndex FROM cteSequenceTable

    OPTION (MAXRECURSION 32767)

    RETURN

END