SQL Server版本升级后出现兼容性问题如何解决？

Question

升级到新版SQL Server后，部分旧版T-SQL语法或函数行为发生变化，导致原有存储过程或查询报错。例如，`ORDER BY`子句中引用未在`SELECT`列表中出现的列，在兼容性级别低于110时被允许，但在更高版本默认设置下会引发错误。此外，数据类型隐式转换规则变更（如`datetime`与`varchar`之间的转换）也可能中断应用执行。如何在不重写大量代码的前提下，快速定位并解决此类兼容性问题？

Answer 1

1. 理解SQL Server版本升级中的兼容性变化

在将数据库从旧版SQL Server（如2008 R2或2012）升级至新版（如2019或2022）时，T-SQL行为的变更可能导致现有存储过程、视图或查询失败。这些变更主要源于：

• 兼容性级别调整：例如，兼容性级别110以下允许ORDER BY引用未出现在SELECT列表中的列，而120及以上默认禁止此行为。
• 隐式数据类型转换规则收紧：新版对datetime与varchar之间的自动转换更严格，可能引发“Conversion failed”错误。
• 函数弃用与语义变更：如RAISERROR与THROW的交互逻辑、OFFSET FETCH在事务中的行为等。

2. 常见兼容性问题分类与示例

问题类型	旧版行为（≤100）	新版行为（≥130）	典型报错信息
ORDER BY 非SELECT列	允许	报错（除非使用UNION或TOP）	The ORDER BY clause is invalid...
datetime → varchar 隐式转换	宽松转换（如'Jan 1 2020'）	需显式CAST/CONVERT	Conversion failed when converting date...
IDENTITY_INSERT限制	部分场景容忍	严格检查上下文	IDENTITY_INSERT is already ON...
TEXT/NTEXT使用	支持	建议替换为VARCHAR(MAX)	The text, ntext, and image data types...

3. 快速定位兼容性问题的技术路径

1. 启用查询存储（Query Store）：升级后开启Query Store，捕获所有失败查询的执行计划与文本。
2. 利用系统DMV分析错误：

   SELECT 
       q.query_sql_text,
       p.plan_id,
       rs.last_error_message
   FROM sys.query_store_query_text q
   JOIN sys.query_store_query qq ON q.query_text_id = qq.query_text_id
   JOIN sys.query_store_plan p ON qq.query_id = p.query_id
   JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
   WHERE rs.last_error_number IS NOT NULL;
   

3. 设置临时兼容性级别回退：针对关键应用库，可暂时设为110或120以维持运行。

   ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 120;
   

4. 使用Extended Events监控T-SQL异常：创建会话跟踪sql_statement_failed事件。

4. 解决方案策略分层实施

根据影响范围和修复成本，采用分级应对策略：

• 紧急缓解（短期）：通过调整数据库兼容性级别、启用LEGACY_CARDINALITY_ESTIMATION等Trace Flag或Database Scoped Configuration来快速恢复服务。
• 中期重构（可控迭代）：使用静态代码分析工具（如Redgate SQL Prompt、ApexSQL Refactor）扫描T-SQL脚本中不符合新规范的语法结构。
• 长期治理（自动化）：建立CI/CD流程中的T-SQL语法合规性检查，集成到DevOps流水线。

5. 自动化检测与修复建议流程图

graph TD
            A[开始升级前评估] --> B{是否启用Query Store?}
            B -- 是 --> C[部署XEvent会话监控]
            B -- 否 --> D[启用Query Store]
            D --> C
            C --> E[执行预演迁移]
            E --> F[收集失败查询]
            F --> G[解析错误类型]
            G --> H{属于已知兼容性问题?}
            H -- 是 --> I[应用对应修复策略]
            H -- 否 --> J[记录为新问题并上报]
            I --> K[验证修复效果]
            K --> L[生成修复报告]

6. 数据类型转换问题专项处理

对于datetime与varchar转换问题，推荐统一使用标准格式与显式转换：

-- 不推荐（易出错）
    SELECT * FROM Logs WHERE CreateTime > 'Jan 1 2020'

    -- 推荐（明确格式）
    SELECT * FROM Logs WHERE CreateTime > CONVERT(datetime, '2020-01-01', 120)

    -- 或使用DATEFROMPARTS / DATETIME2
    SELECT * FROM Logs WHERE CreateTime > DATEFROMPARTS(2020,1,1)
    

同时可在数据库级别启用ANSI_WARNINGS OFF作为过渡手段（不推荐长期使用）。

7. 兼容性级别与功能对照表

兼容性级别	对应版本	ORDER BY限制	CE模型	推荐状态
100	SQL Server 2008	宽松	旧版	废弃
110	SQL Server 2012	部分限制	旧版	过渡可用
130	SQL Server 2016	严格	新版基数估算	推荐基础
150	SQL Server 2019	严格+智能提示	引入Row Mode Memory Grant Feedback	生产首选
160	SQL Server 2022	增强安全性	支持Scalar UDF Inlining	最新推荐