SQL--兼容级别

转载：ALTER DATABASE (Transact-SQL) 兼容级别
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

SQL Server 2016 (13.x) 之前的 SQL Server 早期版本中跟踪标志 4199 下的修补程序现在默认启用。 具有兼容性模式 130。 跟踪标志 4199 仍适用于 2016 SQL Server (13.x) 之后发布的新查询优化器修补程序。 若要在中使用较旧的查询优化SQL 数据库必须选择兼容级别 110。

较低兼容性级别与级别 120 之间的差异


较低兼容性级别与级别 100 和 110 之间的差异


兼容性级别90和级别100之间的差异




保留关键字
兼容性设置还确定应用程序保留的数据库引擎。 下表显示了每个兼容性级别所引入的保留关键字。

在给定兼容性级别，保留关键字包括在该级别或较低级别引入的所有关键字。 例如，对于兼容性级别为 110 的应用程序，将保留上表列出的所有关键字。 在较低的兼容性级别中，级别 100 的关键字仍保留有效的对象名，但与这些关键字相对应的级别 110 的语言功能将不可用。

一旦引入，关键字便会保持为保留关键字。 例如，在兼容性级别 90 中引入的保留关键字 PIVOT 在级别 100、110 和 120 中也被保留。

如果某一应用程序使用对其保留级别而言是关键字的标识符，则该应用程序将失败。 若要对此进行处理， (请用方括号将标识符括在 []) 或 引号 (") ;例如，若要将使用该标识符的应用程序升级到 兼容级别 90，你可以将标识符更改为 或 “EXTERNAL”。

权限
需要对数据库拥有 ALTER 权限。

示例
A. 更改兼容性级别
以下示例将 AdventureWorks2019sample 数据库的兼容级别更改为 150，这是 SQL Server 2019 (15.x)。

ALTER DATABASE AdventureWorks2019
SET COMPATIBILITY_LEVEL = 150;
GO

以下示例返回当前数据库的兼容级别。

SELECT name, compatibility_level
FROM sys.databases
WHERE name = db_name();
GO

B. 忽略 SET LANGUAGE 语句，兼容性级别 120 或更高级别除外
以下查询将忽略 语句 SET LANGUAGE ，兼容性级别 120 或更高级别除外。

SET DATEFORMAT dmy;
DECLARE @t2 date = '12/5/2011' ;
SET LANGUAGE dutch;
SELECT CONVERT(varchar(11), @t2, 106);
GO

兼容性级别小于 120 时的结果： 12 May 2011

当兼容级别设置为 120 或更高时的结果： 12 mei 2011

C. 对于 110 或更低的兼容级别设置，EXCEPT 子句右侧的递归引用产生无限循环

WITH cte AS 
    (SELECT * FROM (VALUES (1),(2),(3)) v (a)),
r AS 
    (SELECT a FROM cte
    UNION ALL
    (SELECT a FROM cte EXCEPT SELECT a FROM r) 
)
SELECT a
FROM r;
GO

D. 样式 0 与 121 之间的差异
当兼容级别低于 110 CASTCONVERT 时，对 CAST 和 CONVERT 数据类型的 和 操作的默认样式为 121，除非在计算列表达式中使用了任一类型。 对于计算列，默认样式为 0。

当兼容级别为 110 或更高CASTCONVERT时，针对 CAST 和 CONVERT 数据类型的 和 操作的默认样式始终为 121。 有关详细信息 ，请参阅较低兼容性级别与级别 100 和 110 之间的差异。

DROP TABLE IF EXISTS t1;
GO

CREATE TABLE t1 (c1 time(7), c2 datetime2);
GO

INSERT t1 (c1,c2) VALUES (GETDATE(), GETDATE());
GO

SELECT CONVERT(nvarchar(16),c1,0) AS TimeStyle0
       ,CONVERT(nvarchar(16),c1,121)AS TimeStyle121
       ,CONVERT(nvarchar(32),c2,0) AS Datetime2Style0
       ,CONVERT(nvarchar(32),c2,121)AS Datetime2Style121
FROM t1;
GO

这会返回如下所示的结果：

E. 变量赋值 - 顶级 UNION 运算符
在数据库兼容级别设置为 90 下，在包含顶级 UNION 运算符的语句中允许变量赋值，但返回意外结果。 例如，在以下语句中，将来自两个表的联合的 @v 列的值赋给局部变量 BusinessEntityID。 按照定义，如果 SELECT 语句返回多个值，则将返回的最后一个值赋给变量。 在这种情况下，会正确地将最后一个值赋给变量，但还会返回 SELECT UNION 语句的结果集。

ALTER DATABASE AdventureWorks2012
SET compatibility_level = 110;
GO
USE AdventureWorks2012;
GO
DECLARE @v int;
SELECT @v = BusinessEntityID FROM HumanResources.Employee
UNION ALL
SELECT @v = BusinessEntityID FROM HumanResources.EmployeeAddress;
SELECT @v;

在数据库兼容性级别设置为 100 及更高时，在包含顶级 UNION 运算符的语句中不允许变量赋值。 返回错误 10734。

若要纠正该错误，请重写查询，如下例所示。

DECLARE @v int;
SELECT @v = BusinessEntityID FROM
    (SELECT BusinessEntityID FROM HumanResources.Employee
     UNION ALL
     SELECT BusinessEntityID FROM HumanResources.EmployeeAddress) AS Test;
SELECT @v;

后续步骤
有关数据库兼容性级别和相关概念的详细信息，请参阅以下文章：

更改数据库兼容性模式和使用查询存储
兼容性认证
ALTER DATABASE
使用查询优化助手升级数据库
CREATE DATABASE
查看或更改数据库的兼容级别
查询存储提示
适用于：yesSQL Server (所有受支持的版本) Yes Azure SQL 数据库 Yes Azure SQL 托管实例

将 SQL 和查询处理行为设置为与指定的 SQL 引擎版本兼容。 有关其他 ALTER DATABASE 选项，请参阅 ALTER DATABASE。
语法

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

参数
database_name 要修改的数据库的名称。

COMPATIBILITY_LEVEL {150 | 140 | 130 | 120 | 110 | 100 | 90 | 80} 是要使数据库兼容的 SQL Server 版本。 可以配置以下兼容级别值（并非所有版本都支持所有以上列出的兼容级别）：

重要

SQL Server 和 Azure SQL 数据库的数据库引擎版本号之间没有可比性，它们分别是这两项产品的内部版本号。 适用于 Azure SQL 数据库的数据库引擎与 SQL Server 数据库引擎基于相同的代码基础映像。 最重要的是，Azure SQL 数据库中的数据库引擎始终具有 SQL 数据库引擎的最新功能。 Azure SQL 数据库版本 12 比 SQL Server 版本 15 更新。

备注
对于 SQL Server 的所有安装，默认的兼容级别与数据库引擎的版本相关联。 新数据库将设置为此级别，除非模型数据库的兼容性级别较低。 对于附加的数据库或从任何早期版本的 SQL Server 还原的数据库，数据库将保留其现有的兼容级别，前提是该实例至少允许 SQL Server 的该实例。 如果移动的数据库的兼容性级别低于允许级别，数据库引擎会自动将数据库设置为允许的最低兼容级别。 这既适用于系统数据库也适用于用户数据库。

当附加或还原数据库时，以及在就地升级之后，SQL Server 2017 (4.x) 应满足以下行为：

如果升级前用户数据库的兼容级别为 100 或更高，升级后将保持相应级别。
如果升级前用户数据库的兼容级别为90，则在升级后的数据库中，兼容级别将设置为100，这是 SQL Server 2017 (4.x) 中支持的最低兼容级别。
tempdb、model、msdb 和 Resource 数据库的兼容级别将设置为给定数据库引擎版本的默认兼容级别。
master 系统数据库保留它在升级之前的兼容性级别。 这不会影响用户数据库行为。
对于在较低的兼容级别运行的预先存在的数据库，只要应用程序不需要使用仅在更高的数据库兼容性级别中可用的增强功能，就可以使用有效的方法维护以前的数据库兼容性级别。 对于新的开发工作，或者当现有应用程序需要使用智能查询处理和一些新的 SQL 的新功能时，请计划将数据库兼容性级别升级到最新版本。 有关详细信息，请参阅兼容性级别和数据库引擎升级。

备注

如果没有用户对象和依赖项，则升级到默认兼容性级别通常是安全的。 有关详细信息，请参阅建议 - master 数据库。
使用 ALTER DATABASE 更改数据库的兼容性级别。 当发出 USE 命令或使用该数据库作为默认数据库上下文来处理新登录时，数据库的新兼容性级别设置会生效。 若要查看数据库的当前兼容级别，请查询compatibility_level目录视图中的 compatibility_level 列。

备注

在 SQL Server 的早期版本中创建的分发数据库，并且升级到 SQL Server 2016 (13. x) RTM 或 Service Pack 1 的兼容级别为90，这对于其他数据库不受支持。 这并不影响复制功能。 升级到更高版本的服务包和 SQL Server 版本将导致分发数据库的兼容性级别增加到可与主数据库匹配。

备注

从2019 年11月起，在 Azure SQL 数据库中，新创建的数据库的默认兼容级别为150。 Microsoft 不会更新现有数据库的数据库兼容级别。 这是由客户自行决定的。 Microsoft 强烈建议客户计划升级到最新兼容性级别，以利用最新查询优化改进。

若要对数据库整体使用数据库兼容性级别 120 或更高，但选择使用映射到数据库兼容性级别 110 的 SQL Server 2012 (11.x) 的基数估计模型，请参阅 ALTER DATABASE SCOPED CONFIGURATION，特别是其关键字 。

若要详细了解如何评估 Azure SQL 数据库 上两个不同兼容级别之间最重要的查询的性能差异，请参阅 Azure SQL 数据库 中的改进查询性能与兼容性级别 130。 本文引用兼容性级别 130 和 SQL Server，但相同的方法适用于升级到 SQL Server 和 Azure SQL 数据库 中的 140 或更高级别。

若要确定连接到数据库引擎的版本，请执行以下查询。

SELECT SERVERPROPERTY('ProductVersion');

并非所有因兼容级别而异的功能都受 Azure SQL 数据库。
若要确定当前兼容级别，请查询 sys.databases 的 compatibility_level 列。

SELECT name, compatibility_level FROM sys.databases;

兼容性级别和数据库引擎升级
数据库兼容性级别是一个有价值的工具，它通过允许升级 SQL Server 数据库引擎，同时通过保持相同的升级前数据库兼容性级别来保持连接应用程序的相同功能状态，从而帮助实现数据库现代化。 这意味着，可以从较旧版本的 SQL Server (（例如 SQL Server 2008) ）升级到 SQL Server 或 Azure SQL 数据库 (（托管实例) ， (数据库连接) 除外）。 有关详细信息，请参阅兼容性认证。

只要应用程序不需要使用仅在更高数据库兼容性级别中提供的增强功能，就一种有效的方法可以升级 SQL Server 数据库引擎 并维护以前的数据库兼容性级别。 有关使用兼容性级别实现后向兼容性的详细信息，请参阅兼容性认证。

升级数据库兼容性级别最佳做法
有关升级兼容性级别的建议工作流，请参阅更改数据库兼容性模式和使用查询存储。 此外，有关升级数据库兼容性级别的辅助体验，请参阅 使用查询优化助手升级数据库。

兼容性级别和存储过程
执行存储过程时，它使用定义存储过程的数据库的当前兼容级别。 在更改某一数据库的兼容性设置时，该数据库的所有存储过程都将随之自动重新编写。

使用兼容性级别实现后向兼容性
数据库兼容性级别设置提供与早期版本的 SQL Server 的向后兼容性，其中与 Transact-SQL 和查询优化行为相关，仅适用于指定数据库，而不是整个服务器。

从兼容性模式 130 开始，任何影响修补程序和功能的新查询计划都被特意地仅添加到新兼容性级别中。 这样做是为了最大限度地减少在升级过程中由于以下原因而引发的风险：新查询优化行为可能引入的查询计划更改导致性能降低。

从应用程序的角度来看，在通过相关的兼容性级别设置控制的行为中，使用更低的兼容性级别作为更安全的迁移路径可解决版本差异。 目标仍应是在某个时间点升级到最新的兼容性级别，以便继承某些新功能（例如智能查询处理），但此目标将以受控方式完成。

有关更多详细信息（包括升级数据库兼容性级别的建议工作流）请参阅 升级数据库兼容性级别最佳做法。

重要

给定版本引入的已停用SQL Server不受兼容性级别保护。 这是指从应用程序中删除SQL Server 数据库引擎。 例如，该FASTFIRSTROW提示在 2012 SQL Server 11.x (中已) ，并替换为 OPTION (FAST n ) 提示。 将数据库兼容级别设置为 110 不会恢复废止的提示。

要详细了解已停用的功能，请参阅 SQL Server 中停用的数据库引擎功能和 SQL Server 2014 中停用的数据库引擎功能。

重要

给定版本引入的中断性SQL Server版本可能不受兼容性级别保护。 这是指不同版本之间的行为SQL Server 数据库引擎。 Transact-SQL行为通常受兼容级别保护。 但是，已更改或删除的系统对象不受兼容级别保护。

受兼容级别保护的一个重大更改示例是从 datetime 到 datetime2 数据类型的隐式转换。 在数据库兼容级别 130 以下，通过考虑导致不同转换值的毫秒小数部分，这些转换显得更加准确。 若要还原以前的转换行为，请将数据库兼容级别设置为 120 或更低。

兼容级别不保护的重大更改示例有：

系统对象中更改了列名。 在 SQL Server 2012 (11.x) 中，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 ‘AdventureWorks2016’, ‘autoshrink’, ‘FALSE’; 都会生成错误 2812（找不到存储过程“sp_dboption”）。
若要详细了解重大更改，请参阅 SQL Server 2019 中的数据库引擎功能重大更改、SQL Server 2017 中的数据库引擎功能重大更改、SQL Server 2016 中的数据库引擎功能重大更改和 SQL Server 2014 中的数据库引擎功能重大更改。

兼容性级别之间的差异
对于所有 SQL Server，默认兼容级别与 数据库引擎 版本相关联，如下表所示。 对于新的开发工作，请始终计划在最新的数据库兼容性级别认证应用程序。

新的 Transact-SQL 语法不受数据库兼容性级别的影响，除非它们可以通过创建与用户 Transact-SQL冲突来中断现有应用程序。 本文的后续部分介绍了这些例外，并概述了特定兼容级别之间的差异。

数据库兼容性级别还提供与早期版本的 SQL Server 的向后兼容性，因为从任何早期版本的 SQL Server 附加或还原的数据库将保留其现有的兼容性级别 (如果与允许的最低兼容级别) 相同或更高。 本文的使用兼容性级别实现向后兼容性部分对此进行了介绍。

从数据库兼容性级别 130 开始，影响查询计划的任何新修补程序和功能仅添加到可用的最新兼容性级别，也称为默认兼容性级别。 这样做是为了最大限度地减少在升级过程中由于以下原因而引发的风险：新查询优化行为可能引入的查询计划更改导致性能降低。

仅添加到新版本版本的默认兼容级别的基本计划影响更改数据库引擎为：

针对跟踪标志 4199 下SQL Server早期版本发布的查询优化器修补程序将在较新版本的默认兼容级别SQL Server启用。 适用于：SQL Server ( 2016 SQL Server 2016 (13.x) ) 和 Azure SQL 数据库。

例如，发布 2016 SQL Server 13.x (2016) 时， 对于使用 SQL Server 2016 (13.x) 默认兼容级别 (130) 的数据库，为以前的 SQL Server 版本 (和相应的兼容性级别 100 到 120) 自动启用了所有查询优化器修补程序。 只需显式启用后期 RTM 的查询优化器修补程序。

备注

若要启用查询优化器修补程序，可以使用以下方法：

对于服务器级别，使用跟踪标志 4199。
在数据库级别，使用 QUERY_OPTIMIZER_HOTFIXESQUERY_OPTIMIZER_HOTFIXES。
在查询级别，通过修改 USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES’USE HINT ‘ENABLE_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 的查询优化器修补程序。

下表对此行为进行了汇总：

解决错误结果或访问冲突错误的查询优化器修补程序不受跟踪标志 4199 的保护。 这些修补程序并不被视为可选项。

对SQL Server 和 Azure SQL 数据库上发布的基数估计器的更改仅在新数据库引擎版本的默认兼容级别中启用，而不是在以前的兼容级别上启用。

例如，当 SQL Server 2016 (13. x) 发布时，对基数估算过程的更改仅适用于使用 SQL Server 2016 () () 130 默认兼容级别的数据库。 以前的兼容级别保留了在 SQL Server 2016 (13. x) 之前可用的基数估计行为。

稍后，当 SQL Server 2017 (14. x) 发布时，对基数估算过程的较新更改仅适用于使用 SQL Server 2017 (2.x) () 140 默认兼容级别的数据库。 数据库兼容性级别130保留 SQL Server 2016 (十三) 基数估计行为。

下表对此行为进行了汇总：

同样适用于 Azure SQL 数据库。

重要

本文的后续部分会介绍特定兼容性级别之间的其他差异。

兼容性级别140和级别150之间的差异
此部分介绍了随兼容性级别 150 一起引入的新行为。

有关在数据库兼容性级别150中启用的查询处理功能的详细信息，请参阅SQL Server 2019 中的新增功能和SQL 数据库中的智能查询处理。

兼容性级别130和级别140之间的差异
本节介绍随兼容级别 140 引入的新行为。

SQL Server 2017 之前的早期 SQL Server 版本中处于跟踪标志 4199 下的修补程序现在默认情况下会启用。 具有兼容性模式 140。 跟踪标志 4199 仍会适用于在 SQL Server 2017 之后发布的新查询优化器修补程序。 有关跟踪标志 4199 的信息，请参阅跟踪标志 4199。

兼容性级别 120 和级别 130 之间的差异
本节介绍随兼容级别 130 引入的新行为。

建议的内容
(sp_changedbowner SQL) - SQL Server
sp_changedbowner (Transact-SQL)
TRUSTWORTHY 数据库属性 - SQL Server
了解 TRUSTWORTHY 数据库属性，该属性指明 SQL Server 实例是否信任该数据库及其内容。 默认为 OFF。
(sp_configure SQL) - SQL Server
sp_configure (Transact-SQL)
DBCC CHECKDB (Transact-SQL) - SQL Server
DBCC CHECKDB (Transact-SQL)
DBCC CLONEDATABASE (Transact-SQL) - SQL Server
DBCC CLONEDATABASE (Transact-SQL)
升级兼容性级别会降低性能 - SQL Server
本文提供在将数据库兼容性级别从 120 升级到 130 时发生的问题的解决方案。
sp_change_users_login (Transact-SQL) - SQL Server
sp_change_users_login (Transact-SQL)
ALTER DATABASE 文件和文件组 - SQL Server (Transact-SQL)
使用 Transact-SQL 更新数据库的文件和文件组。