MSSQL Server sample: Given a master table (RangeInfo) and a detail table (SeqRecords) with sequence ranges for batch ‘B001’, find missing numbers (e.g., 101, 203, 204, 205, 206, 207, 208, 209).
-- Define master range table
CREATE TABLE RangeInfo (BatchCode VARCHAR(10), StartVal INT, EndVal INT);
INSERT INTO RangeInfo (BatchCode, StartVal, EndVal) VALUES ('B001', 1, 3000), ('B001', 3000, 5000);
-- Define sequence records table
CREATE TABLE SeqRecords (RecID INT, BatchCode VARCHAR(10), BeginSeq INT, FinishSeq INT);
INSERT INTO SeqRecords (RecID, BatchCode, BeginSeq, FinishSeq) VALUES (1, 'B001', 1, 100), (2, 'B001', 102, 202), (3, 'B001', 210, 222);
In my experience, the most effective method is to make use of a numerical sequence generator combined with strategic joins against the existing sequence records. I often employ a recursive CTE or a dedicated numbers table for this purpose. This approach generates a complete sequence within each defined range from the master table. Comparing the full set with the detail records allows for identification of absent values in an efficient manner. When working with extensive datasets, ensuring proper indexing and testing performance under realistic loads is crucial for success.
hey, try using a tally table, it can be simpler. generate a full sequence from your range then left join with seqrecords to spot gaps. works fine if indexing is good. sometimes recursive cte just adds needless complexity 
hey im now trying a sequence function to generate numbers on the fly. anyone used window functions instead? curious how it compares vs the tally tables in large datasets. what sort of hiccups did you run into?