MS SQL脚本优化咨询：高效检测字段是否偏离默认值

1. 替换IIF+EXISTS为批量聚合查询 #

当前逐字段用IIF+EXISTS判断的写法会重复扫描表数据，对1.5TB级数据库IO开销极大。建议改成按字段统计偏离默认值的行数，单表扫描一次即可处理所有目标字段：

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    COUNT(CASE 
        WHEN c.default_object_id IS NOT NULL 
             AND c.value <> COALESCE(OBJECT_DEFINITION(c.default_object_id), '') 
        THEN 1 
    END) AS ModifiedRowCount
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.is_ms_shipped = 0 -- 排除系统表
GROUP BY t.name, c.name

只要ModifiedRowCount > 0就说明字段有过修改，避免了多次全表扫描。

2. 提前筛选有默认值的字段 #

不要遍历3000+所有字段，先通过系统视图筛选出带默认约束的字段，减少无效检测：

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    dc.definition AS DefaultValue
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE t.is_ms_shipped = 0

基于这个结果集生成检测逻辑，只处理真正需要检查的字段。

3. 优化临时表使用 #

如果#TableUsage是逐行插入，大结果集下磁盘写入开销高。可以改用内存优化表（SQL Server 2014+支持）或表变量：

CREATE TABLE #TableUsage (
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    IsModified BIT
) WITH (MEMORY_OPTIMIZED = ON);

表变量适合结果集较小的场景，内存优化表则能大幅降低IO等待。

4. 分批次处理表 #

一次性扫描200+表会瞬间拉高数据库IO，分批次处理能减轻压力：

DECLARE @BatchSize INT = 10;
DECLARE @TableList TABLE (ID INT IDENTITY, TableName NVARCHAR(128));
INSERT INTO @TableList SELECT name FROM sys.tables WHERE is_ms_shipped = 0;

DECLARE @CurrentID INT = 1;
WHILE @CurrentID <= (SELECT MAX(ID) FROM @TableList)
BEGIN
    SELECT t.name INTO #BatchTables 
    FROM @TableList 
    WHERE ID BETWEEN @CurrentID AND @CurrentID + @BatchSize - 1;

    -- 生成并执行当前批次表的检测SQL
    -- 动态SQL逻辑省略
    
    DROP TABLE #BatchTables;
    SET @CurrentID += @BatchSize;
    WAITFOR DELAY '00:00:10'; -- 可选：批次间停顿缓解IO
END

5. 利用索引缩小扫描范围 #

如果表有更新时间戳字段，只检测近期修改的数据：

SELECT TOP 1 1 
FROM YourTable
WHERE UpdateTime > DATEADD(MONTH, -3, GETDATE())
AND ColumnValue <> COALESCE(OBJECT_DEFINITION(DEFAULT_OBJECT_ID), '')

对高频修改的字段创建非聚集索引，避免全表扫描；用TOP 1优化EXISTS判断，找到一条修改记录就停止扫描。

6. 动态SQL批量生成脚本 #

手动编写200+表的检测代码效率极低，用动态SQL批量生成：

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL += '
INSERT INTO #TableUsage
SELECT ''' + t.name + ''', ''' + c.name + ''', 
IIF(EXISTS(SELECT TOP 1 1 FROM ' + QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' <> COALESCE(OBJECT_DEFINITION(' + CAST(c.default_object_id AS NVARCHAR) + '), '''')), 1, 0);'
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.default_object_id IS NOT NULL AND c.default_object_id <> 0;

EXEC sp_executesql @SQL;

用QUOTENAME避免SQL注入风险，同时TOP 1能大幅缩短单字段的检测时间。

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