SQL Server 2016下查找缺失TicketNr的高效解决方案求助

针对你这个400万行数据的场景，小表常用的生成全量序列再对比的方法肯定会慢到离谱，这里给你两个适配大数据量的高效方案，还有关键的性能优化点：

方案一：利用系统表生成连续范围，左连接找缺失 #

这个方法的核心是快速生成首张到末张TicketNr之间的所有连续数字，再和现有数据对比找出缺失值，避免递归CTE的性能瓶颈：

1. 先获取范围边界：

DECLARE @MinTicketNr INT, @MaxTicketNr INT;
SELECT 
    @MinTicketNr = MIN(TicketNr), 
    @MaxTicketNr = MAX(TicketNr)
FROM dbo.Ticket
WHERE TicketNr IS NOT NULL; -- 过滤空值，空值不在我们要找的范围内

2. 生成连续数字范围并匹配查找：

WITH NumberRange AS (
    SELECT TOP (@MaxTicketNr - @MinTicketNr + 1)
        @MinTicketNr + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Nr
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT Nr AS MissingTicketNr
FROM NumberRange nr
LEFT JOIN dbo.Ticket t ON nr.Nr = t.TicketNr
WHERE t.TicketNr IS NULL
ORDER BY Nr;

为什么这个方法高效？ #

• sys.all_columns是SQL Server自带的系统表，本身有几千条数据，交叉连接后能快速生成数百万甚至上亿条连续行，完全覆盖400万的范围需求。
• 不需要额外创建数字表，直接利用系统资源，维护成本低。

方案二：用窗口函数找间隙，按需生成缺失值 #

如果你的工单数据大部分是连续的，只有少量间隙，这个方法会更高效——它只处理有间隙的区间，不用生成全量数字：

DECLARE @MinTicketNr INT, @MaxTicketNr INT;
SELECT 
    @MinTicketNr = MIN(TicketNr), 
    @MaxTicketNr = MAX(TicketNr)
FROM dbo.Ticket
WHERE TicketNr IS NOT NULL;

WITH TicketGaps AS (
    -- 用LEAD获取当前工单的下一个工单编号
    SELECT 
        TicketNr AS CurrentNr,
        LEAD(TicketNr) OVER (ORDER BY TicketNr) AS NextNr
    FROM dbo.Ticket
    WHERE TicketNr IS NOT NULL
),
GapRanges AS (
    -- 提取存在缺失的区间
    SELECT 
        CurrentNr + 1 AS StartGap,
        NextNr - 1 AS EndGap
    FROM TicketGaps
    WHERE NextNr > CurrentNr + 1 -- 只保留有间隙的记录
)
-- 生成每个间隙里的所有缺失编号
SELECT 
    StartGap + sv.number - 1 AS MissingTicketNr
FROM GapRanges
JOIN master.dbo.spt_values sv 
    ON sv.type = 'P' -- 只取正数序列
    AND sv.number BETWEEN 1 AND (EndGap - StartGap + 1)
ORDER BY MissingTicketNr;

这个方法的优势： #

• 只聚焦于有缺失的区间，当缺失数据占比低时，处理的数据量会远小于全量范围，性能提升明显。
• master.dbo.spt_values是系统自带的数字表，用来生成区间内的连续数非常高效。

关键性能优化建议 #

不管用哪个方案，一定要做这一步：

• 给TicketNr字段创建非聚集索引：

CREATE NONCLUSTERED INDEX IX_Ticket_TicketNr ON dbo.Ticket(TicketNr);

这个索引会让窗口函数排序、左连接匹配的速度提升几个数量级，400万行的数据下效果尤为明显。

另外，记得始终过滤掉TicketNr为空的行，避免不必要的计算开销。

内容的提问来源于stack exchange，提问作者Lowkey