生成100亿唯一数字集记录遇类型冲突错误求助

错误根源 #

1. GENERATE_SERIES参数类型不匹配：调用GENERATE_SERIES(1, 10000000000)时，第一个参数1是INT类型，第二个参数10000000000是BIGINT类型，违反了函数要求所有输入参数类型一致的规则，这是触发Msg 5373的直接原因。
2. HASHBYTES返回类型导致排序冲突：HASHBYTES('MD5', ...)生成的Randomizer是VARBINARY类型，大数据量分组排序时，SQL Server内部处理会出现类型不兼容问题，引发Msg 206的类型冲突错误。
3. 超大数据量一次性生成的资源瓶颈：一次性生成100亿条记录的写法远超SQL Server的内存和IO承载能力，这也是小数据量（5000万）能运行、大数据量报错的核心原因之一。

修正后的代码 #

1. 基础修正版（参数与随机逻辑修复） #

INSERT GenNumbers (NumID, Number1, Number2, Number3, Number4, Number5, Number6)
SELECT NumID
        ,Number1
        ,Number2
        ,Number3
        ,Number4
        ,Number5
        ,Number6
FROM (
SELECT
    S.Value AS NumID,
    MAX(CASE WHEN N.RowNum = 1 THEN N.Number END) AS Number1,
    MAX(CASE WHEN N.RowNum = 2 THEN N.Number END) AS Number2,
    MAX(CASE WHEN N.RowNum = 3 THEN N.Number END) AS Number3,
    MAX(CASE WHEN N.RowNum = 4 THEN N.Number END) AS Number4,
    MAX(CASE WHEN N.RowNum = 5 THEN N.Number END) AS Number5,
    ABS(CHECKSUM(NEWID()) % 50) + 1 AS Number6
-- 修正GENERATE_SERIES参数为统一BIGINT类型
FROM GENERATE_SERIES(CAST(1 AS BIGINT), 10000000000) S 
CROSS APPLY (
    SELECT
        S2.value AS Number,
        ROW_NUMBER() OVER(ORDER BY R.Randomizer) AS RowNum
    FROM GENERATE_SERIES(1, 100) S2
    CROSS APPLY(
        -- 改用CHECKSUM返回INT类型，避免VARBINARY的类型冲突
        SELECT CHECKSUM(NEWID(), S.value, S2.value) AS Randomizer
    ) R
) N
GROUP BY S.Value
) GN;

2. 分批次插入版（推荐，解决资源瓶颈） #

100亿条记录一次性插入完全不现实，建议分批次循环生成，每次插入1000万~1亿条：

DECLARE @Start BIGINT = 1;
DECLARE @BatchSize BIGINT = 10000000; -- 每次生成1000万条
DECLARE @End BIGINT = 10000000000;

WHILE @Start <= @End
BEGIN
    INSERT GenNumbers (NumID, Number1, Number2, Number3, Number4, Number5, Number6)
    SELECT NumID
            ,Number1
            ,Number2
            ,Number3
            ,Number4
            ,Number5
            ,Number6
    FROM (
    SELECT
        S.Value AS NumID,
        MAX(CASE WHEN N.RowNum = 1 THEN N.Number END) AS Number1,
        MAX(CASE WHEN N.RowNum = 2 THEN N.Number END) AS Number2,
        MAX(CASE WHEN N.RowNum = 3 THEN N.Number END) AS Number3,
        MAX(CASE WHEN N.RowNum = 4 THEN N.Number END) AS Number4,
        MAX(CASE WHEN N.RowNum = 5 THEN N.Number END) AS Number5,
        ABS(CHECKSUM(NEWID()) % 50) + 1 AS Number6
    FROM GENERATE_SERIES(@Start, @Start + @BatchSize - 1) S 
    CROSS APPLY (
        SELECT
            S2.value AS Number,
            ROW_NUMBER() OVER(ORDER BY CHECKSUM(NEWID(), S.value, S2.value)) AS RowNum
        FROM GENERATE_SERIES(1, 100) S2
    ) N
    GROUP BY S.Value
    ) GN;

    SET @Start = @Start + @BatchSize;
    -- 可选：添加等待时间，避免资源占用过高
    WAITFOR DELAY '00:00:10';
END

额外优化建议 #

• 关闭表的自动统计信息更新：ALTER TABLE GenNumbers SET AUTO_UPDATE_STATISTICS OFF;，批量插入完成后再开启，减少插入时的统计开销。
• 禁用触发器（如果存在）：避免触发器干扰插入性能。
• 预生成随机排序结果：提前生成1-100的随机排序数据集，重复使用，减少CROSS APPLY的重复计算量。

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