如何快速查找带前缀索引varchar列的最小可用序列值？

你的循环写法效率低的核心原因是每次循环都要单独执行一次表查询，当数据量达到数千条时，重复查询的开销会被急剧放大。下面给出两种基于集合操作的高效实现方式，避免循环带来的性能损耗：

方法一：利用递归CTE生成连续数字匹配 #

这种方法先提取列中的数字部分，再生成从1开始的连续数字序列，找到其中不在现有数字集合里的最小值：

WITH NumberCTE AS (
    -- 初始生成数字1
    SELECT 1 AS Num
    UNION ALL
    -- 递归生成后续数字，上限设为当前最大数字+1（避免生成过多无用数字）
    SELECT Num + 1 
    FROM NumberCTE
    WHERE Num < (SELECT ISNULL(MAX(CAST(RIGHT(Col, 5) AS INT)), 0) + 1 FROM MyTable)
)
SELECT 'Next Number: ABC' + FORMAT(MIN(Num), '00000') AS NextNumber
FROM NumberCTE
-- 筛选出现有表中不存在的数字
WHERE Num NOT IN (SELECT CAST(RIGHT(Col, 5) AS INT) FROM MyTable);

说明： #

• RIGHT(Col, 5) 是针对你示例中5位数字的提取方式，如果数字位数不固定，可以改用 SUBSTRING(Col, PATINDEX('%[0-9]%', Col), LEN(Col)) 来通用提取数字部分
• 递归CTE的上限设置为当前最大数字+1，避免生成超出必要范围的数字，减少计算量

方法二：用窗口函数查找数字间隙 #

这种方法通过窗口函数LEAD找出数字序列中的间隙，直接定位最小的缺失值：

WITH ExtractedNums AS (
    -- 提取并转换列中的数字为整数
    SELECT CAST(RIGHT(Col, 5) AS INT) AS Num
    FROM MyTable
),
OrderedNums AS (
    -- 对数字排序，同时获取下一个数字
    SELECT 
        Num, 
        LEAD(Num) OVER (ORDER BY Num) AS NextNum
    FROM ExtractedNums
    -- 额外加入一条记录，处理从1开始就缺失的情况
    UNION ALL
    SELECT 0, MIN(Num) FROM ExtractedNums
)
SELECT TOP 1 
    'Next Number: ABC' + FORMAT(
        CASE
            -- 如果当前数字和下一个数字之间有间隙，取间隙的起始值
            WHEN Num + 1 < NextNum THEN Num + 1
            -- 如果所有数字连续，取最大数字+1
            ELSE (SELECT MAX(Num) + 1 FROM ExtractedNums)
        END, 
        '00000'
    ) AS NextNumber
FROM OrderedNums
-- 筛选存在间隙的记录，或者初始检查1是否缺失的记录
WHERE Num + 1 < NextNum OR Num = 0
ORDER BY Num;

说明： #

• 窗口函数LEAD可以高效获取当前数字的下一个数字，一次扫描就能找出所有间隙
• 针对表为空的情况，会自动返回ABC00001，符合需求

两种方法都只需要对表进行几次扫描，利用你已建立的索引可以进一步提升查询速度，相比循环写法性能会有明显提升。

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