合 MSSQL中的数据库兼容性级别
Tags： MSSQLSQL Server整理自官网兼容性级别
- 简介
- 参数
- database_name
- COMPATIBILITY_LEVEL { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
- 备注
- Azure SQL 备注
- 兼容性级别和数据库引擎升级
- 兼容性级别和存储过程
- 使用兼容性级别实现后向兼容性
- 兼容性级别之间的差异
- 兼容性级别 150 和兼容性级别 160 之间的差异
- 兼容性级别 140 和兼容性级别 150 之间的差异
- 兼容性级别 130 和兼容性级别 140 之间的差异
- 兼容性级别 120 和兼容性级别 130 之间的差异
- 较低兼容性级别和级别 120 之间的差异
- 低兼容性级别与级别 100 和 110 之间的差异
- 兼容性级别 90 和兼容性级别 100 之间的差异
- 保留关键字
- 权限
- 示例
- A. 更改兼容性级别
- B. 忽略 SET LANGUAGE 语句（除非低于兼容性级别 120 或更高）
- C. 对于 110 或更低的兼容级别设置，EXCEPT 子句右侧的递归引用产生无限循环
- D. 样式 0 与 121 之间的差异
- E. 变量赋值 - 顶级 UNION 运算符
- 总结
- 参考
简介
将 Transact-SQL 和查询处理行为设置为与指定的 SQL 引擎版本兼容。 有关其他 ALTER DATABASE 选项，请参阅 ALTER DATABASE。
1 2 | ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 } |
参数
database_name
要修改的数据库的名称。
COMPATIBILITY_LEVEL { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
要使数据库与之兼容的 SQL Server 版本。 可以配置以下兼容级别值（并非所有版本都支持所有以上列出的兼容级别）：
展开表
| Products | 数据库引擎版本 | 默认兼容性级别标示 | 支持的兼容级别值 |
|---|---|---|---|
| Azure SQL 数据库 | 16 | 150 | 160、150、140、130、120、110、100 |
| Azure SQL 托管实例 | 16 | 150 | 160、150、140、130、120、110、100 |
| SQL Server 2022 (16.x) | 16 | 160 | 160、150、140、130、120、110、100 |
| SQL Server 2019 (15.x) | 15 | 150 | 150、140、130、120、110、100 |
| SQL Server 2017 (14.x) | 14 | 140 | 140、130、120、110、100 |
| SQL Server 2016 (13.x) | 13 | 130 | 130、120、110、100 |
| SQL Server 2014 (12.x) | 12 | 120 | 120、110、100 |
| SQL Server 2012 (11.x) | 11 | 110 | 110、100、90 |
| SQL Server 2008 R2 (10.50.x) | 10.5 | 100 | 100、90、80 |
| SQL Server 2008 (10.0.x) | 10 | 100 | 100、90、80 |
| SQL Server 2005 (9.x) | 9 | 90 | 90、80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |
重要
SQL Server 和 Azure SQL 数据库的数据库引擎版本号之间没有可比性，它们分别是这两项产品的内部版本号。 适用于 Azure SQL 数据库的数据库引擎与 SQL Server 数据库引擎基于相同的代码基础映像。 最重要的是，Azure SQL 数据库中的数据库引擎始终具有 SQL 数据库引擎的最新功能。 Azure SQL 数据库 的版本 12 高于 SQL Server 的版本 15。
备注
对于所有 SQL Server 安装，默认兼容性级别都与 数据库引擎 的版本相关联。 新数据库将设置为此级别，除非 model 数据库的兼容性级别较低。 对于从 SQL Server 的任何早期版本附加或还原的数据库，如果数据库的兼容性级别是该 SQL Server 实例允许的最低级别，则将保留其现有的兼容性级别。 移动兼容性级别低于 数据库引擎 允许级别的数据库会自动将数据库设置为允许的最低兼容性级别。 这既适用于系统数据库也适用于用户数据库。
附加或还原数据库时，以及就地升级后，SQL Server 2017（14.x）需要以下行为：
- 如果升级前用户数据库的兼容级别为 100 或更高，升级后将保持相应级别。
- 如果升级前用户数据库的兼容级别为 90，则在升级后的数据库中，兼容级别将设置为 100，该级别为 SQL Server 2017 (14.x) 支持的最低兼容级别。
- 在给定的数据库引擎版本中，
tempdb、model、msdb和 Resource 数据库的兼容性级别将设置为默认兼容性级别。 master系统数据库保留它在升级之前的兼容级别。 这不会影响用户数据库行为。
对于在较低的兼容性级别运行的预先存在的数据库，只要应用程序不需要使用仅在更高数据库兼容性级别中可用的增强功能，它就是维护之前的数据库兼容性级别的有效方法。 对于新的开发工作，或当现有应用程序需要使用新功能（如智能查询处理）以及一些新的 Transact-SQL 时，请计划将数据库兼容性级别升级到可用的最新级别。 有关详细信息，请参阅兼容性级别和数据库引擎升级。
备注
如果没有用户对象和依赖项，则升级到默认兼容性级别通常是安全的。 有关详细信息，请参阅建议 - master 数据库。
使用 ALTER DATABASE 更改数据库的兼容性级别。 当发出 USE <database> 命令或使用该数据库作为默认数据库上下文来处理新登录时，数据库的新兼容性级别设置会生效。 若要查看数据库的当前兼容级别，请查询 sys.databases 目录视图中的 compatibility_level 列。
在早期版本 SQL Server 中创建并已升级到 SQL Server 2016 (13.x) RTM 或 Service Pack 1 的分发数据库采用兼容性级别 90，其他数据库不支持该级别。 这并不影响复制功能。 升级到更高版本的服务包和 SQL Server 版本将导致分发数据库的兼容性级别增加到可与 master 数据库匹配。
若要对整个数据库使用数据库兼容性级别 120 或更高级别，但选择启用映射到数据库兼容性级别 110 的 SQL Server 2012 (11.x)基数估计模型，请参阅 ALTER DATABASE SCOPED CONFIGURATION，尤其是它的关键字 LEGACY_CARDINALITY_ESTIMATION = ON。
若要确定当前兼容级别，请查询 sys.databases 的 compatibility_level 列。
SQL
1 | SELECT name, compatibility_level FROM sys.databases; |
Azure SQL 备注
从 2019 年 11 月起，在 Azure SQL 数据库和 Azure SQL 托管实例中，新创建的数据库的默认兼容性级别。 Microsoft 不会更新现有数据库的数据库兼容性级别。 这是由客户自行决定的。 Microsoft 强烈建议客户计划升级到最新兼容性级别，以使用最新查询优化改进。
若要详细了解如何评估你最重要的查询在 Azure SQL 数据库 上的两个不同兼容性级别的性能差异，请参阅已改善 Azure SQL 数据库中兼容性级别 130 的查询性能。 本文介绍兼容性级别 130 和 SQL Server，但在 SQL Server 和 Azure SQL 数据库 中也可以使用相同的方法升级到 140 或更高级别。
若要确定连接到的数据库引擎版本，请执行以下查询。
SQL
1 | SELECT SERVERPROPERTY('ProductVersion'); |
Azure SQL 数据库上并不支持所有功能（因兼容级别而异）。
兼容性级别和数据库引擎升级
数据库兼容性级别是一个重要的工具，可通过允许升级 SQL Server 数据库引擎，同时通过维持相同的升级前数据库兼容性级别保持连接应用程序的功能状态相同，从而帮助实现数据库现代化。 这意味着，无需更改应用程序（数据库连接除外），即可从 SQL Server 的较旧版本（例如 SQL Server 2008 (10.0.x)）升级到 SQL Server 或 Azure SQL 数据库（包括 Azure SQL 托管实例）。 有关详细信息，请参阅兼容性认证。
只要应用程序不需要使用仅在更高数据库兼容性级别中可用的增强功能，它就是升级 SQL Server 数据库引擎和维护之前的数据库兼容性级别的有效方法。 有关使用兼容性级别实现后向兼容性的详细信息，请参阅兼容性认证。
兼容性级别和存储过程
执行某一存储过程时，该存储过程将使用定义它的数据库的当前兼容性级别。 在更改某一数据库的兼容性设置时，该数据库的所有存储过程都将随之自动重新编写。
使用兼容性级别实现后向兼容性
数据库兼容性级别设置提供与 SQL Server 早期版本的后向兼容性，在与 Transact-SQL 和查询优化行为相关的方面，后向兼容性仅适用于指定的数据库，而不是整个服务器。
从兼容性模式 130 开始，任何影响修补程序和功能的新查询计划都被特意地仅添加到新兼容性级别中。 这样做是为了最大限度地减少在升级过程中由于以下原因而引发的风险：新查询优化行为可能引入的查询计划更改导致性能降低。
从应用程序的角度来看，在通过相关的兼容性级别设置控制的行为中，使用更低的兼容性级别作为更安全的迁移路径可解决版本差异。 目标仍应是在某个时间点升级到最新的兼容性级别，以便继承某些新功能（例如智能查询处理），但此目标将以受控方式完成。
有关更多信息（包括升级数据库兼容性级别的建议工作流），请参阅升级数据库兼容性级别的最佳做法。
给定的 SQL Server 版本中引入的已停用功能不受兼容性级别保护 。 这指的是从 SQL Server 数据库引擎 中删除的功能。 例如，
FASTFIRSTROW提示在 SQL Server 2012 (11.x) 中废止，并替换为OPTION (FAST n )提示。 将数据库兼容性级别设置为 110 不会恢复废止的提示。 要详细了解已停用的功能，请参阅 SQL Server 中停用的数据库引擎功能。给定的 SQL Server 版本中引入的中断性变更可能不受兼容性级别保护 。 这指的是 SQL Server 数据库引擎 版本之间的行为变更。 Transact-SQL 行为通常受兼容级别保护。 但是，已更改或删除的系统对象不受兼容级别保护。
受兼容级别保护的中断性变更的示例是从日期时间到 \datetime2 数据类型的隐式转换**。** 在数据库兼容级别 130 以下，通过考虑导致不同转换值的毫秒小数部分，这些转换显得更加准确。 若要还原以前的转换行为，请将数据库兼容级别设置为 120 或更低。
兼容级别不保护的重大更改示例有：
- 系统对象中更改了列名。 在 SQL Server 2012 (11.x) 中，
sys.dm_os_sys_info中的single_pages_kb列已重命名为pages_kb。 无论兼容级别如何，查询SELECT single_pages_kb FROM sys.dm_os_sys_info都会生成错误 207（列名无效）。 - 删除了系统对象。 在 SQL Server 2012 (11.x) 中，
sp_dboption已删除。 无论兼容级别如何，该语句EXEC sp_dboption 'AdventureWorks2022', 'autoshrink', 'FALSE';都会生成错误 2812 （Couldn't find stored procedure 'sp_dboption'）。
若要详细了解重大更改，请参阅 SQL Server 2019 中的数据库引擎功能重大更改、SQL Server 2017 中的数据库引擎功能重大更改、SQL Server 2016 中的数据库引擎功能重大更改和 SQL Server 2014 中的数据库引擎功能重大更改。
- 系统对象中更改了列名。 在 SQL Server 2012 (11.x) 中，
兼容性级别之间的差异
对于所有 SQL Server 安装，默认兼容性级别都与 数据库引擎 版本相关联，如此表中所示。 对于新的开发工作，请始终计划在最新的数据库兼容性级别上验证应用程序。
新的 Transact-SQL 语法不受数据库兼容性级别的限制，除非它们可以通过创建与用户 Transact-SQL 代码的冲突来破坏现有应用程序。 本文的后续部分介绍了这些例外，并概述了特定兼容级别之间的差异。
数据库兼容性级别还提供与 SQL Server 早期版本的向后兼容性，因为从任何 SQL Server 早期版本附加和还原的数据库会保留其现有的兼容性级别（如果等于或高于允许的最低兼容性级别）。 本文的使用兼容性级别实现向后兼容性部分对此进行了介绍。
从数据库兼容性级别 130 开始，任何影响查询计划的新修补程序和功能仅会添加到可用的最新兼容性级别（也称为默认兼容性级别）。 这样做是为了最大限度地减少在升级过程中由于以下原因而引发的风险：新查询优化行为可能引入的查询计划更改导致性能降低。
以下是仅添加到新版本 数据库引擎 默认兼容性级别、影响计划的基本更改：
针对跟踪标志 4199 下的 SQL Server 早期版本发布的查询优化器修补程序在 SQL Server 较新版本的默认兼容性级别中自动启用。
适用于：SQL Server（从版本 SQL Server 2016 （13.x）开始），Azure SQL 数据库。
例如，发布 SQL Server 2016 (13.x) 时，为使用 SQL Server 2016 (13.x) 默认兼容性级别 (130) 的数据库自动启用了针对 SQL Server 早期版本（相应的兼容性级别为 100 至 120）发布的所有查询优化器修补程序。 只需显式启用后期 RTM 的查询优化器修补程序。
若要启用查询优化器修补程序，可以使用以下方法：
- 对于服务器级别，使用跟踪标志 4199。
- 对于数据库级别，使用 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的
QUERY_OPTIMIZER_HOTFIXES选项。 - 在查询级别，通过修改查询来使用
USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'查询提示。 - 在查询级别，在不更改代码的情况下，使用
USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'并使用查询存储提示（预览版）功能。
之后，发布 SQL Server 2017 (14.x) 时，为使用 SQL Server 2017 (14.x) 默认兼容性级别 (140) 的数据库自动启用了在 SQL Server 2016 (13.x) RTM 之后发布的所有查询优化器修补程序。 这是一种累积行为，还会包括所有早期版本的修补程序。 同样，只需显式启用后期 RTM 的查询优化器修补程序。
下表对此行为进行了汇总：
展开表
数据库引擎 (DE) 版本 数据库兼容性级别 TF 4199 来自所有以前的数据库兼容性级别的 QO 更改 DE 版本后期 RTM 的 QO 更改 13 (SQL Server 2016 (13.x)) 100 至 120 130 关闭 启用 关闭 启用 已禁用 已启用 已启用 已启用 已禁用 已启用 已禁用 已启用 14 (SQL Server 2017 (14.x)) 100 至 120 130 140 关闭 启用 关闭 启用 关闭 启用 已禁用 已启用 已启用 已启用 已启用 已启用 已禁用 已启用 已禁用 已启用 已禁用 已启用 15 (SQL Server 2019 (15.x)) 和 12 (Azure SQL 数据库) 100 至 120 130 至 140 150 关闭 启用 关闭 启用 关闭 启用 已禁用 已启用 已启用 已启用 已启用 已启用 已禁用 已启用 已禁用 已启用 已禁用 Enabled 16 (SQL Server 2022 (16.x)) 和 12（Azure SQL 数据库） 100 至 120 130 至 150 160 关闭 启用 关闭 启用 关闭 启用 已禁用 已启用 已启用 已启用 已启用 已启用 已禁用 已启用 已禁用 已启用 已禁用 Enabled 解决错误结果或访问冲突错误的查询优化器修补程序不受跟踪标志 4199 的保护。 这些修补程序并不被视为可选项。
对 SQL Server 和 Azure SQL 数据库 上发布的基数估算器的更改仅在数据库引擎新版本的默认兼容性级别中启用，未在之前的兼容性级别上启用。
例如，发布 SQL Server 2016 (13.x) 时，对基数估算过程的更改仅对使用 SQL Server 2016 (13.x) 默认兼容性级别 (130) 的数据库可用。 之前的兼容性级别保留了 SQL Server 2016 (13.x) 之前可用的基数估算行为。
之后，发布 SQL Server 2017 (14.x) 时，对基数估算过程的新更改仅对使用 SQL Server 2017 (14.x) 默认兼容性级别 (140) 的数据库可用。 数据库兼容性级别 130 保留了 SQL Server 2016 (13.x) 基数估算行为。
下表对此行为进行了汇总：
展开表
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
