ALTER DATABASE 兼容性级别 (Transact-SQL)

  • 项目

 

将某些数据库行为设置为与指定的 SQL Server 版本兼容。有关其他 ALTER DATABASE 选项，请参阅 ALTER DATABASE (Transact-SQL)。

主题链接图标Transact-SQL 语法约定

适用范围：SQL Server（SQL Server 2008 到当前版本） Azure SQL Database。

语法

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

参数

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

  • COMPATIBILITY_LEVEL {80 | 90 | 100 | 110 | 120 }
    要使数据库与之兼容的 SQL Server 版本。该值必须为下列值之一：

    值

    说明

    适用于

    80

    SQL Server 2000

    SQL Server 2008 至 SQL Server 2008 R2

    90

    SQL Server 2005

    SQL Server 2008 至 SQL Server 2012

    100

    SQL Server 2008 和 SQL Server 2008 R2

    SQL Server 2008 至 SQL Server 2014

    110

    SQL Server 2012

    SQL Server 2012 至 SQL Server 2014

    120

    SQL Server 2014

    SQL Server 2014 至 SQL Server 2014

备注

对于所有 SQL Server 2014 安装，默认的兼容性级别都为 120。除非 model 数据库有更低的兼容性级别，否则 SQL Server 2014 中创建的数据库会设置为该级别。在将数据库从 SQL Server 2014 的任何早期版本升级到 SQL Server 时，如果数据库的兼容性级别不在 100 以下，则该数据库将保留其现有的兼容性级别。升级兼容性级别为 90 的数据库会将数据库的兼容性级别设置为 100。这既适用于系统数据库也适用于用户数据库。使用 ALTER DATABASE 可更改数据库的兼容性级别。若要查看数据库的当前兼容性级别，请查询 sys.databases 目录视图中的 compatibility_level 列。

利用兼容性级别获得向后兼容

兼容性级别只影响指定数据库的行为，而不影响整个服务器的行为。兼容性级别只实现与 SQL Server 的早期版本保持部分向后兼容。通过将兼容性级别用作临时性的迁移辅助工具，可解决相关兼容性级别设置控制的行为之间存在的版本差异问题。如果现有 SQL Server 应用程序受到 SQL Server 2014 中行为差异的影响，请对该应用程序进行转换，使之能正常运行。然后使用 ALTER DATABASE 将兼容性级别更改为 120。数据库的新兼容性设置将在该数据库下次成为当前数据库（无论是在登录时作为默认数据库还是在 USE 语句中指定）时生效。

最佳实践

如果在用户连接到数据库时更改兼容性级别，可能会使活动查询产生不正确的结果集。例如，如果在编写查询计划时兼容性级别发生更改，则编写后的计划可能同时基于旧的和新的兼容性级别，从而造成计划不正确，并可能导致结果不准确。此外，如果将计划放在计划缓存中供后续的查询重用，则问题可能更加复杂。为了避免查询结果不准确，建议您使用以下过程来更改数据库的兼容性级别：

  1. 通过使用 ALTER DATABASE SET SINGLE_USER，将数据库设置为单用户访问模式。

  2. 更改数据库的兼容性级别。

  3. 通过使用 ALTER DATABASE SET MULTI_USER，将数据库设为多用户访问模式。

  4. 有关设置数据库访问模式的详细信息，请参阅 ALTER DATABASE (Transact-SQL)。

兼容性级别和存储过程

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

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

本节介绍随兼容性级别 120 引入的新行为。

兼容性级别设置为 110 或更低

兼容性级别设置为 120

使用旧版查询优化器。

SQL Server 2014 包括了对创建和优化查询计划的组件的显著改进。这个新的查询优化器功能依赖于使用数据库兼容性级别 120。若要利用这些改进，应使用数据库兼容性级别 120 开发新的数据库应用程序。应对从较早版本的 SQL Server 中迁移的应用程序进行仔细测试，以便确认保持或改进了好的性能。如果性能下降，可以将数据库兼容性级别设置为 110 或更低，以便使用较早的查询优化器方法。

数据库兼容级别 120 使用针对现代数据仓库和 OLTP 工作负荷进行优化的新基数估计器。在因为性能问题将数据库兼容性级别设置为 110 前，请参阅 SQL Server 2014新增功能（数据库引擎） 主题的“查询计划”一节中的建议。

如果兼容性级别低于 120，则在将 date 值转换为字符串值时语言设置将被忽略。请注意，此行为仅特定于 date 类型。例如，只有兼容性级别低于 120 时，以下查询才会忽略 SET LANGUAGE 语句。

SET DATEFORMAT dmy; 
DECLARE @t2 date = '12/5/2011' ;
SET LANGUAGE dutch; 
SELECT CONVERT(varchar(11), @t2, 106); 
-- Results when the compatibility level is less than 120. 
12 May 2011 
-- Results when the compatibility level is set to 120).
12 mei 2011

将 date 值转换为字符串值时，不忽略语言设置。

EXCEPT 子句右侧的递归引用产生无限循环。下例演示此行为。

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

EXCEPT 子句中的递归引用产生遵从 ANSI SQL 标准的错误。

递归 CTE 允许列名重复。

递归 CTE 不允许列名重复。

如果更改触发器，则启用禁用的触发器。

更改触发器不更改触发器的状态（已启用或已禁用）。

OUTPUT INTO 表子句忽略 IDENTITY_INSERT SETTING = OFF，并允许插入显式值。

将 IDENTITY_INSERT 设置为 OFF 后，不能为表中的标识列插入显式值。

将数据库包含设置为部分包含后，验证 MERGE 语句的 OUTPUT 子句中的 $action 字段可能会返回排序规则错误。

MERGE 语句的 $action 子句返回的值的排序规则是数据库排序规则而非服务器排序规则，因此不会返回排序规则冲突错误。

SELECT INTO 语句始终创建单线程插入操作。

SELECT INTO 语句可创建并行插入操作。插入大量行时，并行操作可提高性能。

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

本节介绍随兼容性级别 110 引入的新行为。此部分也适用于级别 120。

兼容性级别设置为 100 或更低

至少为 110 的兼容性级别设置

公共语言运行时 (CLR) 数据库对象用 CLR 的版本 4 执行。但会避免在 CLR 的版本 4 中引入的某些行为更改。有关详细信息，请参阅CLR 集成中的新增功能。

CLR 数据库对象用 CLR 的版本 4 执行。

XQuery 函数 string-length 和 substring 将每个代理项计为两个字符。

XQuery 函数 string-length 和 substring 将每个代理项计为一个字符。

在递归公用表表达式 (CTE) 查询中允许 PIVOT。然而，当每个分组有多个行时，该查询返回不正确的结果。

在递归公用表表达式 (CTE) 查询中不允许 PIVOT。将返回错误。

RC4 算法仅用于支持向后兼容性。仅当数据库兼容级别为 90 或 100 时，才能使用 RC4 或 RC4_128 对新材料进行加密。（建议不要使用。）在 SQL Server 2012 中，可以通过任何兼容性级别对使用 RC4 或 RC4_128 加密的材料进行解密。

不能使用 RC4 或 RC4_128 加密新材料。而是使用一种较新的算法，如 AES 算法之一。在 SQL Server 2012 中，可以通过任何兼容性级别对使用 RC4 或 RC4_128 加密的材料进行解密。

对 time 和 datetime2 数据类型的 CAST 和 CONVERT 操作的默认样式为 121，当在计算列表达式中使用这些类型时除外。对于计算列，默认样式为 0。当创建用于涉及自动参数化的查询中或约束定义中的计算列时，此行为会影响计算列。

下面的示例显示样式 0 和 121 之间的差异。它并不演示上面所述的行为。有关日期和时间样式的详细信息，请参阅 CAST 和 CONVERT (Transact-SQL)。

CREATE TABLE t1 (c1 time(7), c2 datetime2); 
INSERT t1 (c1,c2) VALUES (GETDATE(), GETDATE());
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;
-- Returns values such as the following.
TimeStyle0       TimeStyle121     
Datetime2Style0      Datetime2Style121
---------------- ---------------- 
-------------------- --------------------------
3:15PM           15:15:35.8100000 
Jun  7 2011  3:15PM  2011-06-07 15:15:35.8130000

兼容性级别为 110 时，对 time 和 datetime2 数据类型的 CAST 和 CONVERT 操作的默认样式始终为 121。如果您的查询依赖旧行为，请使用低于 110 的兼容性级别或在受影响的查询中显式指定 0 样式。

将数据库升级到兼容性级别 110 将不更改已存储到磁盘的用户数据。您必须相应手动更正此数据。例如，如果您使用了 SELECT INTO 来从包含上述计算列表达式的源创建表，将存储数据（使用样式 0）而非存储计算列定义本身。您需要手动更新此数据，以匹配样式 121。

在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 datetime。本地表中相应的列（在选择列表中的相同序号位置中）必须为 datetime 类型。

在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 smalldatetime。本地表中相应的列（在选择列表中的相同序号位置中）必须为 smalldatetime 类型。

在升级到 110 后，分布式分区视图将由于数据类型不匹配而失败。您可以通过将针对远程表的数据类型更改为 datetime 或者将本地数据库的兼容性级别设置为 100 或更低，解决上述问题。

SOUNDEX 函数实现以下规则：

  1. 当分隔两个具有相同 SOUNDEX 代码的辅音时，将忽略大写 H 或大写 W。

  2. 如果 character_expression 的前 2 个字符具有相同的 SOUNDEX 代码，则将包含这两个字符。如果一组并行辅音具有相同的 SOUNDEX 代码，则将不包含它们，第一个辅音除外。

SOUNDEX 函数实现以下规则：

  1. 如果大写 H 或大写 W 分隔具有相同 soundex 代码的两个辅音，则将忽略右侧的辅音。

  2. 如果一组并行辅音具有相同的 SOUNDEX 代码，则将不包含它们，第一个辅音除外。

其他规则可能导致由 SOUNDEX 函数计算的值不同于在更低数据库兼容性级别时计算的值。在升级到兼容性级别 110 后，可能需要重新生成使用 SOUNDEX 函数的索引、堆或 CHECK 约束。有关详细信息，请参阅SOUNDEX (Transact-SQL)

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

本节介绍随兼容性级别 100 引入的新行为。

兼容性级别设置为 90

兼容性级别设置为 100

影响的可能性

对于多语句表值函数，在创建它们时，无论会话级别设置如何，QUOTED_IDENTIFER 设置始终为 ON。

在创建多语句表值函数时，会遵循 QUOTED IDENTIFIER 会话设置。

中

在创建或更改分区函数时，会评估函数中的 datetime 和 smalldatetime 文字，并假定语言设置为 US_English。

使用当前语言设置来评估该分区函数中的 datetime 和 smalldatetime 文字。

中

INSERT 和 SELECT INTO 语句中允许使用（但会忽略）FOR BROWSE 子句。

INSERT 和 SELECT INTO 语句中不允许使用 FOR BROWSE 子句。

中

OUTPUT 子句中允许使用全文谓词。

OUTPUT 子句中不允许使用全文谓词。

低

不支持 CREATE FULLTEXT STOPLIST、ALTER FULLTEXT STOPLIST 和 DROP FULLTEXT STOPLIST。系统非索引字表自动与新的全文检索相关联。

支持 CREATE FULLTEXT STOPLIST、ALTER FULLTEXT STOPLIST 和 DROP FULLTEXT STOPLIST。

低

MERGE 不作为保留关键字强制应用。

MERGE 是完全保留的关键字。在 100 和 90 兼容性级别下，都支持 MERGE 语句。

低

使用 INSERT 语句的 <dml_table_source> 参数会引发语法错误。

您可以捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果，然后将这些结果插入目标表或视图。这通过使用 INSERT 语句的 <dml_table_source> 参数来实现。

低

除非指定 NOINDEX，否则 DBCC CHECKDB 或 DBCC CHECKTABLE 将对单个表或索引视图及其所有非聚集索引和 XML 索引同时执行物理和逻辑一致性检查。不支持空间索引。

除非指定 NOINDEX，否则 DBCC CHECKDB 或 DBCC CHECKTABLE 将对单个表及其所有非聚集索引同时执行物理和逻辑一致性检查。但是，在默认情况下，仅对 XML 索引、空间索引和索引视图执行物理一致性检查。

如果指定了 WITH EXTENDED_LOGICAL_CHECKS，则将对索引视图、XML 索引和空间索引（如果存在）执行逻辑检查。默认情况下，先执行物理一致性检查，然后执行逻辑一致性检查。如果还指定了 NOINDEX，则仅执行逻辑检查。

低

如果将 OUTPUT 子句和数据操作语言 (DML) 语句一起使用，并且在语句执行过程中发生运行时错误，则会终止并回滚整个事务。

如果将 OUTPUT 子句和数据操作语言 (DML) 语句一起使用，并且在语句执行过程中发生运行时错误，则行为取决于 SET XACT_ABORT 设置。如果 SET XACT_ABORT 设置为 OFF，则由使用 OUTPUT 子句的 DML 语句所生成的语句中止错误将终止该语句，但批处理的执行仍会继续，并且不会回滚事务。如果 SET XACT_ABORT 设置为 ON，则由使用 OUTPUT 子句的 DML 语句所生成的全部运行时错误都将终止批处理，并回滚事务。

低

CUBE 和 ROLLUP 不作为保留关键字强制应用。

CUBE 和 ROLLUP 是 GROUP BY 子句中的保留关键字。

低

对 XML anyType 类型的元素应用严格验证。

对 XML anyType 类型的元素应用宽松验证。有关详细信息，请参阅通配符组成部分和内容验证。

低

数据操作语言语句不能查询或修改特殊属性 xsi:nil 和 xsi:type。

这意味着 /e/@xsi:nil 失败，同时 /e/@* 忽略 xsi:nil 和 xsi:type 属性。但是，/e 返回 xsi:nil 和 xsi:type 属性，以保持与 SELECT xmlCol 的一致性，即使 xsi:nil = "false" 也是如此。

特殊属性 xsi:nil 和 xsi:type 作为常规属性存储，不能查询和修改。

例如，执行查询 SELECT x.query('a/b/@*') 会返回包括 xsi: nil 和 xsi: type 在内的所有属性。若要在查询中排除这些类型，请用 @*[namespace-uri(.) != "insert xsi namespace uri" 替换 @*，而不是用 (local-name(.) = "type" 或 local-name(.) ="nil". 来替换。

低

用于将 XML 常量字符串值转换为 SQL Server datetime 类型的用户定义函数被标记为确定的。

用于将 XML 常量字符串值转换为 SQL Server datetime 类型的用户定义函数被标记为不确定的。

低

不完全支持 XML 联合和列表类型。

完全支持联合和列表类型，包括以下功能：

  • 列表的联合

  • 联合的联合

  • 原子类型的列表

  • 联合的列表

低

当视图或内联表值函数中包含 xQuery 方法时，不对该方法所需的 SET 选项进行验证。

当视图或内联表值函数中包含 xQuery 方法时，对该方法所需的 SET 选项进行验证。如果该方法的 SET 选项设置不正确，将引发一个错误。

低

包含行尾字符（回车符和换行符）的 XML 属性值不根据 XML 标准进行规范化。即返回回车符和换行符，而不是单个换行符。

包含行尾字符（回车符和换行符）的 XML 属性值会根据 XML 标准进行规范化。也就是说，外部已分析实体（包括文档实体）中的所有换行符都会在输入时进行规范化，方法是将两字符序列 #xD #xA 和后面没有跟 #xA 的所有 #xD 都转换为单个 #xA 字符。

使用属性来传输包含行尾字符的字符串值的应用程序接收到的这些字符将和提交时有所不同。若要避免规范化过程，请使用 XML 数字字符实体对所有行尾字符进行编码。

低

ROWGUIDCOL 和 IDENTITY 列属性可能错误地命名为约束。例如，CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) 语句可以执行，但约束名不会保留，也无法让用户访问。

ROWGUIDCOL 和 IDENTITY 列属性不能命名为约束。返回错误 156。

低

使用双向赋值（如 UPDATE T1 SET @v = column_name = <expression>）来更新列会产生意外后果，因为在语句执行过程中，可以在其他子句（如 WHERE 和 ON 子句）中使用变量的实时值，而不是使用语句起始值。这会导致谓词的含义无法预测地逐行变化。

只有在兼容性级别设置为 90 时，此行为才适用。

使用双向赋值来更新列会产生预期的结果，因为在语句执行过程中，只会访问列的语句起始值。

低

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

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

在包含顶级 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;

低

ODBC 函数 {fn CONVERT()} 使用语言的默认日期格式。对于有些语言，默认格式为 YDM，这会导致在将 CONVERT() 与要求使用 YMD 格式的其他函数（如 {fn CURDATE()}）结合使用时出现转换错误。

在转换为 ODBC 数据类型 SQL_TIMESTAMP、SQL_DATE、SQL_TIME、SQLDATE、SQL_TYPE_TIME 和 SQL_TYPE_TIMESTAMP 时，ODBC 函数 {fn CONVERT()} 使用样式 121（一种独立于语言的 YMD 格式）。

低

ODBC 函数 {fn CURDATE()} 仅返回“YYYY-MM-DD”格式的日期。

ODBC 函数 {fn CURDATE()} 同时返回日期和时间，例如“YYYY-MM-DD hh:mm:ss”。

低

日期时间内部函数（如 DATEPART）不需要字符串输入值，即可成为有效的日期时间文字。例如，SELECT DATEPART (year, '2007/05-30') 可以成功编译。

日期时间内部函数（如 DATEPART）需要字符串输入值，才能成为有效的日期时间文字。在使用无效的日期时间文字时，会返回错误 241。

低

保留关键字

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

兼容性级别设置

保留关键字

120

无。

110

WITHIN GROUP、TRY_CONVERT、SEMANTICKEYPHRASETABLE、SEMANTICSIMILARITYDETAILSTABLE、SEMANTICSIMILARITYTABLE

100

CUBE、MERGE、ROLLUP

90

EXTERNAL、PIVOT、UNPIVOT、REVERT、TABLESAMPLE

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

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

如果某一应用程序使用对其保留级别而言是关键字的标识符，则该应用程序将失败。若要解决这一问题，请用方括号 ([]) 或引号 ("") 括起该标识符；例如，若要将使用标识符 EXTERNAL 的应用程序升级为兼容性级别 90，可以将该标识符更改为 [EXTERNAL] 或 "EXTERNAL"。

有关详细信息，请参阅保留关键字 (Transact-SQL)。

权限

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

示例

A.更改兼容性级别

以下示例将 AdventureWorks2012 数据库的兼容性级别更改为 110,SQL Server 2012。

ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110;
GO

请参阅

ALTER DATABASE (Transact-SQL)
保留关键字 (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)