SQL Server 大宽表批量生成随机测试数据的方法与实践

一、问题背景与需求拆解

在日常开发、测试、性能调优以及数据迁移校验中，测试数据的构造是一项非常基础但又容易出错的工作。尤其是当表结构非常宽，例如包含 200 列，并且列类型混合了整型、浮点型、定长字符串、日期时间等类型时，手工编写插入语句几乎不可行。

本次要解决的需求可以拆解为以下几个技术点：

1. 表共有约 200 列，数据类型包括 int、real、nchar(50)、datetime；
2. 所有非标识列都应生成随机数据；
3. 表中存在 IDENTITY_INSERT 为 OFF 的列，不能显式插入该列；
4. 生成过程中应避免 LOB 类型结果被截断；
5. 方案需要具备可重复执行、可调整数据量、易于维护等特点。

虽然需求中出现了“某个智能对话工具”与“另一个智能对话工具”的使用体验比较，但本文并不讨论哪个工具更好用，而是聚焦于它们都可能遇到的一类共同技术场景：如何基于自然语言需求，落地为可执行的 SQL Server 数据生成方案。无论使用哪一种工具获得初始脚本，理解背后的数据库机制仍然是保证脚本正确性的关键。

二、表结构与约束分析

假设目标表结构简化如下，但实际列数接近 200：

CREATE TABLE dbo.WideTable
(
    Id          INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ColInt1     INT NULL,
    ColInt2     INT NULL,
    ColReal1    REAL NULL,
    ColText1    NCHAR(50) NULL,
    ColDate1    DATETIME NULL
    -- 实际还有约 195 列
);

在这个结构中，Id 是标识列，默认 IDENTITY_INSERT 为 OFF。此时如果显式向 Id 插入值，会报错：

An explicit value for the identity column in table 'WideTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

因此生成脚本必须在列清单中排除该标识列。

同时需要注意，nchar(50) 属于定长 Unicode 字符串类型。虽然它不是 LOB 类型，但在拼接生成 SQL 或使用某些字符串处理函数时，如果结果被隐式转换为 varchar(max) 或 nvarchar(max)，可能存在截断风险。题目中强调“使用 LOB 类型避免结果截断”，核心目的就是防止在动态拼接大量列和值时，字符串变量长度超过常规限制。

三、总体方案设计

针对 200 列的大宽表，推荐采用“元数据驱动 + 动态 SQL 生成”的方式。基本流程如下：

1. 从系统目录视图读取目标表的所有列及数据类型；
2. 过滤掉标识列、计算列、时间戳列等不可显式插入的列；
3. 根据每一列的数据类型，生成对应的随机数据表达式；
4. 使用足够大的字符串变量承载动态 SQL，例如 nvarchar(max)；
5. 执行动态 SQL，批量插入目标行数。

该方案的核心优势在于：不用手工枚举 200 个列名，列类型变化后也容易调整，并且生成逻辑可以被复用。

四、随机数据生成表达式设计

不同类型需要不同的随机表达式。下面分别说明。

4.1 整型列

对于 int 类型，可使用 RAND() 或 CHECKSUM(NEWID()) 生成随机整数。推荐使用 CHECKSUM(NEWID())，因为它在同一批次中不会因为每行调用 RAND() 而产生相同值。

示例：

ABS(CHECKSUM(NEWID())) % 100000

该表达式会生成 0 到 99999 之间的随机整数。如果希望包含负数，可以改写为：

ABS(CHECKSUM(NEWID())) % 200001 - 100000

这样可以生成 -100000 到 100000 之间的整数。

4.2 浮点列

对于 real 类型，可以先生成整数随机数，再除以一个缩放因子，从而得到带小数的随机值。

CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS REAL) / 1000.0

这样可以生成 0 到 999.999 之间的浮点数。

也可以使用 RAND() 与 NEWID() 结合：

CAST(RAND(CHECKSUM(NEWID())) * 1000 AS REAL)

但需要注意 RAND() 在批量查询中的行为差异，建议使用 CHECKSUM(NEWID()) 作为随机种子。

4.3 定长字符串列

对于 nchar(50)，需要生成不超过 50 个字符的 Unicode 字符串。可使用 NEWID() 生成唯一标识符，再截取部分字符。由于 NEWID() 的结果包含字母和数字，格式固定，适合作为基础随机字符串。

示例：

CAST(NEWID() AS NVARCHAR(36))

该字符串长度为 36，小于 50，可以安全写入 nchar(50) 列。若需要更接近列长度，可以进行拼接：

LEFT(CONVERT(NVARCHAR(100), NEWID()) + CONVERT(NVARCHAR(100), NEWID()), 50)

这里通过两个 NEWID() 拼接后截取 50 个字符，确保长度接近列宽。由于目标是随机测试数据，不要求语义可读性，这种方案已经足够。

4.4 日期时间列

对于 datetime 类型，常用方式是在一个基准日期上加上随机天数、小时、分钟或秒。

示例：

DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % (365 * 24 * 3600), '2020-01-01')

这表示从 2020 年 1 月 1 日开始，随机增加 0 到一年秒数之间的值。也可按分钟或天生成：

DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 3650, '2010-01-01')

这样可以生成 2010 至 2020 年之间的日期。

五、动态生成插入脚本

接下来需要根据系统目录视图动态生成列清单和值表达式。SQL Server 中常用的目录视图包括：

• sys.columns：列信息；
• sys.types：类型信息；
• sys.tables：表信息；
• sys.schemas：架构信息。

为了避免对标识列显式赋值，需要识别 is_identity = 1 的列并排除。同时还应排除 is_computed = 1 的计算列，以及 timestamp 或 rowversion 列。

示例动态生成逻辑如下：

DECLARE @schemaName NVARCHAR(128) = 'dbo';
DECLARE @tableName  NVARCHAR(128) = 'WideTable';
DECLARE @sql        NVARCHAR(MAX) = '';
DECLARE @columns    NVARCHAR(MAX) = '';
DECLARE @values     NVARCHAR(MAX) = '';
SELECT
    @columns = @columns + QUOTENAME(c.name) + ',',
    @values  = @values  +
        CASE
            WHEN t.name = 'int' THEN
                'ABS(CHECKSUM(NEWID())) % 100000,'
            WHEN t.name = 'real' THEN
                'CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS REAL) / 1000.0,'
            WHEN t.name = 'nchar' THEN
                'LEFT(CONVERT(NVARCHAR(100), NEWID()) + CONVERT(NVARCHAR(100), NEWID()), 50),'
            WHEN t.name = 'datetime' THEN
                'DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % (365 * 24 * 3600), ''2020-01-01''),'
            ELSE
                'NULL,'
        END
FROM sys.columns c
JOIN sys.types t
    ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.WideTable')
  AND c.is_identity = 0
  AND c.is_computed = 0
ORDER BY c.column_id;
-- 去掉末尾逗号
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @values  = LEFT(@values, LEN(@values) - 1);
SET @sql = N'INSERT INTO ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) +
           N' (' + @columns + N') ' +
           N'SELECT TOP (1000) ' + @values +
           N' FROM sys.all_objects a CROSS JOIN sys.all_objects b';
EXEC sp_executesql @sql;

上述脚本的关键点如下：

1. @columns 和 @values 均声明为 NVARCHAR(MAX)，避免拼接 200 列时字符串被截断；
2. 排除 is_identity = 1 的列；
3. 使用 QUOTENAME 防止列名包含特殊字符；
4. 使用 sys.all_objects 交叉连接生成足够多的行；
5. 使用 TOP (1000) 控制插入行数，实际使用时可调整为参数。

六、避免结果截断的关键措施

在 SQL Server 中，普通字符串变量声明为 VARCHAR(8000) 或 NVARCHAR(4000) 时，如果拼接结果超过最大长度，会发生静默截断或报错。对于 200 列的表，列名加上表达式很容易超过 8000 个字符，因此必须使用 MAX 类型。

同时需要注意，在拼接过程中不要将 NVARCHAR(MAX) 隐式转换为普通长度字符串。例如下面的写法可能导致问题：

DECLARE @sql NVARCHAR(4000);
SET @sql = @sql + ...;

一旦 @sql 按 4000 长度声明，后续即使再赋值给 NVARCHAR(MAX)，截断可能已经发生。因此所有参与大段拼接的变量都应从一开始就声明为 NVARCHAR(MAX)。

此外，如果某个列本身就是 LOB 类型，例如 NVARCHAR(MAX) 或 VARBINARY(MAX)，则生成随机值时也要注意表达式返回类型。可以显式使用 CAST(... AS NVARCHAR(MAX)) 避免中间结果为短类型。

七、参数化与批量执行

上述脚本每次执行可插入一批数据。如果需要生成更大量数据，可将其封装为存储过程，输入参数包括：

• 目标表名；
• 插入行数；
• 随机整数范围；
• 随机日期范围；
• 字符串长度等。

示例存储过程骨架：

CREATE PROCEDURE dbo.GenerateRandomData
    @tableName  NVARCHAR(128),
    @rowCount   INT = 1000
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @schemaName NVARCHAR(128) = 'dbo';
    DECLARE @sql        NVARCHAR(MAX) = '';
    DECLARE @columns    NVARCHAR(MAX) = '';
    DECLARE @values     NVARCHAR(MAX) = '';
    SELECT
        @columns = @columns + QUOTENAME(c.name) + ',',
        @values  = @values  +
            CASE
                WHEN t.name = 'int' THEN
                    'ABS(CHECKSUM(NEWID())) % 100000,'
                WHEN t.name = 'real' THEN
                    'CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS REAL) / 1000.0,'
                WHEN t.name = 'nchar' THEN
                    'LEFT(CONVERT(NVARCHAR(100), NEWID()) + CONVERT(NVARCHAR(100), NEWID()), 50),'
                WHEN t.name = 'datetime' THEN
                    'DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % (365 * 24 * 3600), ''2020-01-01''),'
                ELSE
                    'NULL,'
            END
    FROM sys.columns c
    JOIN sys.types t
        ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID(@schemaName + '.' + @tableName)
      AND c.is_identity = 0
      AND c.is_computed = 0
    ORDER BY c.column_id;
    SET @columns = LEFT(@columns, LEN(@columns) - 1);
    SET @values  = LEFT(@values, LEN(@values) - 1);
    SET @sql = N'INSERT INTO ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) +
               N' (' + @columns + N') ' +
               N'SELECT TOP (' + CAST(@rowCount AS NVARCHAR(20)) + N') ' + @values +
               N' FROM sys.all_objects a CROSS JOIN sys.all_objects b';
    EXEC sp_executesql @sql;
END;

执行时：

EXEC dbo.GenerateRandomData @tableName = 'WideTable', @rowCount = 5000;

这种方式将列枚举过程固化在存储过程中，后续调用非常简单。

八、注意事项与常见错误

8.1 标识列误插入

最典型错误是生成列清单时未排除 is_identity = 1 的列。即便表只有一列是标识列，一旦显式插入，且 IDENTITY_INSERT 为 OFF，整个批次都会失败。

可以通过查询确认标识列：

SELECT name, is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.WideTable')
  AND is_identity = 1;

8.2 动态 SQL 字符串截断

200 列的列名和表达式长度可能达到数千甚至上万字符。所有拼接变量必须使用 NVARCHAR(MAX)。同时建议在生成后使用 PRINT LEN(@sql) 查看长度，辅助判断是否存在异常。

8.3 随机数重复问题

如果使用 RAND() 且不结合 NEWID()，在批量插入时可能出现大量重复值。因为 RAND() 在同一批次中可能只被计算一次。推荐使用 CHECKSUM(NEWID()) 作为随机源。

8.4 字符串列长度溢出

对于 nchar(50)，如果随机字符串表达式生成的长度超过 50，插入会失败或被截断。应使用 LEFT(..., 50) 控制长度，或使用 CAST(NEWID() AS NVARCHAR(36)) 这类天然短于列宽的值。

8.5 日期范围控制

如果不限制日期范围，DATEADD 配合过大随机数可能导致超出 datetime 允许的范围。datetime 的范围是 1753-01-01 到 9999-12-31。建议显式设定基准日期和最大偏移量。

九、扩展思路与最佳实践

在大宽表测试数据生成场景中，还可以进一步优化：

1. 按列创建配置表：将每列的随机范围、格式、空值比例等配置存储在元数据表中，生成时读取配置，比硬编码更灵活。
2. 生成空值分布：测试数据中适当包含 NULL，更接近真实环境。可在表达式中按概率返回 NULL，例如：

CASE WHEN ABS(CHECKSUM(NEWID())) % 100 < 10 THEN NULL ELSE ABS(CHECKSUM(NEWID())) % 100000 END

1. 分批提交：对于上亿行数据，可结合循环与事务，每批插入 1 万到 10 万行，避免单事务过大。
2. 记录生成元数据：为每次生成运行记录表名、行数、时间、参数等，便于追踪和复现。
3. 结合监控与备份：在向重要环境写入大批量测试数据前，应确认目标库为测试环境，并提前备份或使用事务回滚方案。

十、总结

为包含 200 列的大宽表批量生成随机数据，关键不在于逐个列手工编写，而在于利用系统目录视图动态识别表结构，根据类型生成适配表达式，并使用 NVARCHAR(MAX) 承载完整动态 SQL。过程中必须严格排除标识列，避免触发 IDENTITY_INSERT 限制；同时注意字符串长度、随机数重复、日期范围等细节。

该方案适用于 SQL Server 上的开发测试、性能基准、压力测试和数据脱敏后的仿真数据构造。掌握这种方法后，无论表结构多宽、类型多复杂，都可以快速生成可靠且可重复的测试数据。对于企业用户而言，如果目标库部署在云数据库或云原生数据库平台上，也可以将上述逻辑封装为定时任务或自动化测试流水线的一部分，进一步提升测试效率与数据治理能力。