SQL Server 2022系统版本化时态表含数据脚本生成优化方案

核心原因 #

系统版本化时态表的GENERATED ALWAYS AS ROW START/END列由SQL Server自动维护，不允许显式插入值，SMO默认生成的INSERT语句会包含这些列，触发Msg 13536错误。

最优方案：SMO脚本生成时自动排除系统生成列 #

利用SMO的API识别时态表的系统生成列，在生成INSERT脚本时主动排除这些列，无需手动修改脚本。

实现步骤 #

1. 通过SMO获取目标时态表的RowStartColumn和RowEndColumn（这两个就是GENERATED ALWAYS类型的列）。
2. 生成脚本时，要么过滤列后自定义生成INSERT语句，要么对SMO生成的原始脚本进行替换处理，移除列列表和VALUES中对应的值。

C#代码示例 #

using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Linq;

// 初始化SMO服务器和数据库对象（示例）
Server server = new Server("your-server");
Database db = server.Databases["your-db"];
Table targetTable = db.Tables["your-temporal-table"];

// 配置脚本生成选项
ScriptingOptions scriptOpts = new ScriptingOptions
{
    ScriptData = true,
    ScriptSchema = true,
    IncludeHeaders = false,
    NoCommandTerminator = false
};

// 获取时态表的系统生成列
Column rowStartCol = targetTable.RowStartColumn;
Column rowEndCol = targetTable.RowEndColumn;

// 生成原始脚本并处理
StringCollection rawScripts = targetTable.Script(scriptOpts);
foreach (string script in rawScripts)
{
    if (script.TrimStart().StartsWith("INSERT INTO", System.StringComparison.OrdinalIgnoreCase))
    {
        // 处理INSERT语句，移除系统生成列
        string cleanedScript = CleanInsertScript(script, rowStartCol.Name, rowEndCol.Name);
        Console.WriteLine(cleanedScript);
    }
    else
    {
        Console.WriteLine(script);
    }
}

// 辅助方法：清理INSERT语句中的GENERATED ALWAYS列
private static string CleanInsertScript(string insertScript, string rowStartCol, string rowEndCol)
{
    // 拆分INSERT列部分和VALUES部分
    int valuesPos = insertScript.IndexOf("VALUES", System.StringComparison.OrdinalIgnoreCase);
    if (valuesPos == -1) return insertScript;

    string colsSegment = insertScript.Substring(0, valuesPos).Trim();
    string valsSegment = insertScript.Substring(valuesPos).Trim();

    // 处理列列表
    int colStart = colsSegment.IndexOf('(') + 1;
    int colEnd = colsSegment.IndexOf(')');
    string[] cols = colsSegment.Substring(colStart, colEnd - colStart).Split(',').Select(c => c.Trim()).ToArray();
    var filteredCols = cols.Where(c => c != rowStartCol && c != rowEndCol).ToList();
    string newColsSegment = $"{colsSegment.Substring(0, colStart)}{string.Join(", ", filteredCols)}{colsSegment.Substring(colEnd)}";

    // 处理VALUES列表（匹配列的位置）
    int valStart = valsSegment.IndexOf('(') + 1;
    int valEnd = valsSegment.IndexOf(')');
    string[] vals = valsSegment.Substring(valStart, valEnd - valStart).Split(',').Select(v => v.Trim()).ToArray();
    
    // 找到要移除的值的索引
    int startIndex = cols.ToList().FindIndex(c => c == rowStartCol);
    int endIndex = cols.ToList().FindIndex(c => c == rowEndCol);
    
    var filteredVals = vals.ToList();
    if (startIndex != -1) filteredVals.RemoveAt(startIndex);
    // 如果END列在START列之后，移除START后END的索引要减1
    if (endIndex != -1)
    {
        if (endIndex > startIndex && startIndex != -1) endIndex--;
        filteredVals.RemoveAt(endIndex);
    }
    
    string newValsSegment = $"{valsSegment.Substring(0, valStart)}{string.Join(", ", filteredVals)}{valsSegment.Substring(valEnd)}";

    return $"{newColsSegment} {newValsSegment}";
}

不推荐的架构调整方案 #

时态表的ROW START/END列必须是GENERATED ALWAYS类型才能维持系统版本化功能，修改为普通列会导致时态表失效，因此不建议通过调整架构解决此问题。

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