SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create a sample Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY
);

-- Insert some sample records with missing values
INSERT INTO Orders (OrderID) VALUES (1001), (1002), (1004), (1006), (1008);

SELECT * FROM Orders;


OrderID


1001

1002

1004

1006

1008


WITH RecursiveSequence
AS (SELECT MIN(OrderID) AS OrderID,
           MIN(OrderID) AS StartRange,
           MAX(OrderID) AS EndRange
    FROM Orders
    UNION ALL
    SELECT OrderID + 1,
           StartRange,
           EndRange
    FROM RecursiveSequence
    WHERE OrderID < EndRange)
SELECT *
FROM RecursiveSequence


OrderID
StartRange
EndRange


1001
1001
1008

1002
1001
1008

1003
1001
1008

1004
1001
1008

1005
1001
1008

1006
1001
1008

1007
1001
1008

1008
1001
1008


-- Use a recursive common table expression (CTE) to find missing values in the sequence
WITH RecursiveSequence
AS (SELECT MIN(OrderID) AS OrderID,
           MIN(OrderID) AS StartRange,
           MAX(OrderID) AS EndRange
    FROM Orders
    UNION ALL
    SELECT OrderID + 1,
           StartRange,
           EndRange
    FROM RecursiveSequence
    WHERE OrderID < EndRange)
-- Select the missing values
SELECT rs.OrderID AS MissingOrderID
FROM RecursiveSequence rs
    LEFT JOIN Orders o
        ON rs.OrderID = o.OrderID
WHERE o.OrderID IS NULL
OPTION (MAXRECURSION 0); -- Set MAXRECURSION to 0 to avoid recursion limit


MissingOrderID


1003

1005

1007

OrderID	StartRange	EndRange
1001	1001	1008
1002	1001	1008
1003	1001	1008
1004	1001	1008
1005	1001	1008
1006	1001	1008
1007	1001	1008
1008	1001	1008