ALTER DATABASE (Transact-SQL) 兼容级别ALTER DATABASE (Transact-SQL) Compatibility Level

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Transact-SQLTransact-SQL 和查询处理行为设置为与指定版本的 SQL Server 数据库引擎SQL Server Database Engine兼容。Sets Transact-SQLTransact-SQL and query processing behaviors to be compatible with the specified version of the SQL Server 数据库引擎SQL Server Database Engine. 有关其他 ALTER DATABASE 选项,请参阅 ALTER DATABASEFor other ALTER DATABASE options, see ALTER DATABASE.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

语法Syntax

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

参数Arguments

database_name database_name
要修改的数据库的名称。Is the name of the database to be modified.

COMPATIBILITY_LEVEL { 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }COMPATIBILITY_LEVEL { 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
要使数据库与之兼容的 SQL ServerSQL Server 版本。Is the version of SQL ServerSQL Server with which the database is to be made compatible. 可以配置以下兼容级别值(并非所有版本都支持所有以上列出的兼容级别):The following compatibility level values can be configured (not all versions supports all of the above listed compatibility level):

ProductProduct 数据库引擎版本Database Engine Version 默认兼容性级别标示Default Compatibility Level Designation 支持的兼容级别值Supported Compatibility Level Values
SQL Server 2019SQL Server 2019 1515 150150 150、140、130、120、110、100150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x)SQL Server 2017 (14.x) 1414 140140 140、130、120、110、100140, 130, 120, 110, 100
Azure SQL DatabaseAzure SQL Database 单一数据库/弹性池single database/elastic pool 1212 140140 150、140、130、120、110、100150, 140, 130, 120, 110, 100
Azure SQL DatabaseAzure SQL Database 托管实例managed instance 1212 140140 150、140、130、120、110、100150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x)SQL Server 2016 (13.x) 1313 130130 130、120、110、100130, 120, 110, 100
SQL Server 2014 (12.x)SQL Server 2014 (12.x) 1212 120120 120、110、100120, 110, 100
SQL Server 2012 (11.x)SQL Server 2012 (11.x) 1111 110110 110、100、90110, 100, 90
SQL Server 2008 R2SQL Server 2008 R2 10.510.5 100100 100、90、80100, 90, 80
SQL Server 2008SQL Server 2008 1010 100100 100、90、80100, 90, 80
SQL Server 2005 (9.x)SQL Server 2005 (9.x) 99 9090 90、8090, 80
SQL Server 2000SQL Server 2000 88 8080 8080

RemarksRemarks

对于所有 SQL ServerSQL Server 安装,默认兼容级别都设置为数据库引擎Database Engine的版本。For all installations of SQL ServerSQL Server, the default compatibility level is set to the version of the 数据库引擎Database Engine. 除非 数据库具有更低的兼容级别,否则数据库会设置为此级别。Databases are set to this level unless the model database has a lower compatibility level. 在将数据库从 SQL ServerSQL Server 的任何早期版本进行升级时,如果数据库至少是该 SQL ServerSQL Server 实例所允许的最低级别,则它会保留现有兼容性级别。When a database is upgraded from any earlier version of SQL ServerSQL Server, the database retains its existing compatibility level, if it is at least minimum allowed for that instance of SQL ServerSQL Server. 升级兼容性级别低于允许级别的数据库会将数据库自动设置为允许的最低兼容性级别。Upgrading a database with a compatibility level lower than the allowed level, automatically sets the database to the lowest compatibility level allowed. 这既适用于系统数据库也适用于用户数据库。This applies to both system and user databases.

附加或还原数据库时以及就地升级后,SQL Server 2017 (14.x)SQL Server 2017 (14.x) 应出现以下行为:The below behaviors are expected for SQL Server 2017 (14.x)SQL Server 2017 (14.x) when a database is attached or restored, and after an in-place upgrade:

  • 如果升级前用户数据库的兼容级别为 100 或更高,升级后将保持相应级别。If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
  • 如果升级前用户数据库的兼容级别为 90,则在升级后的数据库中,兼容级别将设置为 100,该级别为 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 支持的最低兼容级别。If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x)SQL Server 2017 (14.x).
  • 在给定的 数据库引擎Database Engine 版本中,tempdb、model、msdb 和 Resource 数据库的兼容性级别将设置为默认兼容性级别。The compatibility levels of the tempdb, model, msdb and Resource databases are set to the default compatibility level for a given 数据库引擎Database Engine version.
  • master 系统数据库保留它在升级之前的兼容性级别。The master system database retains the compatibility level it had before upgrade.

使用 ALTER DATABASE 更改数据库的兼容性级别。Use ALTER DATABASE to change the compatibility level of the database. 当发出 USE <database> 命令或使用该数据库作为默认数据库上下文来处理新登录时,数据库的新兼容性级别设置会生效。The new compatibility level setting for a database takes effect when a USE <database> command is issued, or a new login is processed with that database as the default database context. 若要查看数据库的当前兼容级别,请查询 sys.databases 目录视图中的 compatibility_level 列。To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

备注

在早期版本 SQL ServerSQL Server 中创建并已升级到 SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM 或 Service Pack 1 的分发数据库采用兼容性级别 90,其他数据库不支持该级别。A distribution database that was created in an earlier version of SQL ServerSQL Server and is upgraded to SQL Server 2016 (13.x)SQL Server 2016 (13.x) RTM or Service Pack 1 has a compatibility level of 90, which is not supported for other databases. 这并不影响复制功能。This does not have an impact on the functionality of replication. 升级到更高版本的服务包和 SQL ServerSQL Server 版本将导致分发数据库的兼容性级别增加到可与主数据库匹配 。Upgrading to later service packs and versions of SQL ServerSQL Server will result in the compatibility level of the distribution database to be increased to match that of the master database.

从 2018 年 1 月起,在 Azure SQL DatabaseAzure SQL Database 中,新创建的数据库的默认兼容性级别为 140 。As of January 2018, in Azure SQL DatabaseAzure SQL Database, the default compatibility level is 140 for newly created databases. 我们不会更新现有数据库的数据库兼容性级别。We do not update database compatibility level for existing databases. 这是由客户自行决定的。It is up to customers to do at their own discretion. Microsoft 强烈建议客户计划升级到最新兼容性级别,以利用最新查询优化改进。Microsoft highly recommends that customers plan to upgrade to the latest compatibility level in order to leverage the latest query optimization improvements. 若要对整个数据库利用数据库兼容性级别 140,但选择启用映射到数据库兼容性级别 110 的 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 基数估计 模型,请参阅 ALTER DATABASE SCOPED CONFIGURATION,尤其是它的关键字 LEGACY_CARDINALITY_ESTIMATION = ONTo leverage database compatibility level 140 for a database overall, but opt-in to the cardinality estimation model of SQL Server 2012 (11.x)SQL Server 2012 (11.x), which maps to database compatibility level 110, see ALTER DATABASE SCOPED CONFIGURATION, and in particular its keyword LEGACY_CARDINALITY_ESTIMATION = ON.

有关如何评估 Azure SQL DatabaseAzure SQL Database上两个兼容级别之间最重要查询的性能差异的详细信息,请参阅 Improved Query Performance with Compatibility Level 130 in Azure SQL Database(在 Azure SQL 数据库中使用兼容级别 130 提高了查询性能)For details about how to assess the performance differences of your most important queries, between two compatibility levels on Azure SQL DatabaseAzure SQL Database, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database. 请注意,虽然本文中介绍的是兼容性级别 130 和 SQL ServerSQL Server,但同样的方法也适用于为 SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 迁移到 140。Note that this article refers to compatibility level 130 and SQL ServerSQL Server, but the same methodology applies for moves to 140 for SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database.

若要确定连接到的 数据库引擎Database Engine 版本,请执行以下查询。To determine the version of the 数据库引擎Database Engine that you are connected to, execute the following query.

SELECT SERVERPROPERTY('ProductVersion');

备注

Azure SQL DatabaseAzure SQL Database上并不支持所有功能(因兼容级别而异)。Not all features that vary by compatibility level are supported on Azure SQL DatabaseAzure SQL Database.

若要确定当前兼容级别,请查询 sys.databases 的 compatibility_level 列。To determine the current compatibility level, query the compatibility_level column of sys.databases.

SELECT name, compatibility_level FROM sys.databases;

兼容性级别和 SQL Server 升级Compatibility Levels and SQL Server Upgrades

数据库兼容性级别是一个重要的工具,可通过允许升级 SQL Server 数据库引擎SQL Server Database Engine,同时通过维持相同的升级前数据库兼容性级别保持连接应用程序功能状态,从而帮助实现数据库现代化。Database compatibility level is a valuable tool to assist in database modernization, by allowing the SQL Server 数据库引擎SQL Server Database Engine to be upgraded, while keeping connecting applications functional status by maintaining the same pre-upgrade database compatibility level. 只要应用程序不需要利用仅在更高数据库兼容性级别中可用的增强功能,它就是升级 SQL Server 数据库引擎SQL Server Database Engine 和维护之前的数据库兼容性级别的有效方法。As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server 数据库引擎SQL Server Database Engine and maintain the previous database compatibility level. 有关利用兼容性级别获取后向兼容性的详细信息,请参阅后文的利用兼容性级别获得后向兼容性For more information on using compatibility level for backward compatibility, see the Using Compatibility Level for Backward Compatibility later in this article.

对于新的开发工作,或当现有应用程序需要使用新功能以及查询优化器空间中完成的性能改进时,计划将数据库兼容性级别升级到 SQL ServerSQL Server 中可用的最新级别,并验证应用程序可与该兼容性级别一起使用。For new development work, or when an existing application requires use of new features, as well as performance improvements done in the query optimizer space, plan to upgrade the database compatibility level to the latest available in SQL ServerSQL Server, and certify your application to work with that compatibility level. 有关升级数据库兼容性级别的更多详细信息,请参阅后文的升级数据库兼容性级别的最佳做法For more details on upgrading the database compatibility level, see the Best Practices for upgrading Database Compatibility Level later in the article.

提示

如果在给定 SQL ServerSQL Server 版本上测试和验证应用程序,则应用程序在该 SQL ServerSQL Server 版本本机数据库兼容性级别上隐式测试和验证。If an application was tested and certified on a given SQL ServerSQL Server version, then it was implicitly tested and certified on that SQL ServerSQL Server version native database compatibility level.

因此,在使用对应于已测试 SQL ServerSQL Server 版本的数据库兼容性级别时,数据库兼容性级别可为现有应用程序提供简易的认证途径。So, database compatibility level provides an easy certification path for an existing application, when using the database compatibility level corresponding to the tested SQL ServerSQL Server version.

有关兼容性级别之间的差异的详细信息,请参阅后文相应的部分。For more information about differences between compatibility levels, see the appropriate sections later in this article.

若要将 SQL Server 数据库引擎SQL Server Database Engine升级到最新版本,同时维持升级前的数据库兼容性级别及其可支持性状态,建议在数据库(存储过程、函数、触发器等可编程对象)和应用程序(使用捕获应用程序发送的动态代码的工作负荷跟踪)中,使用 Microsoft 数据迁移助手工具 (DMA) 对应用程序代码执行静态函数外围应用验证。To upgrade the SQL Server 数据库引擎SQL Server Database Engine to the latest version, while maintaining the database compatibility level that existed before the upgrade and its supportability status, it is recommended to perform static functional surface area validation of the application code in the database (programmability objects such as stored procedures, functions, triggers, and others) and in the application (using a workload trace that captures the dynamic code sent by the application), by using the Microsoft Data Migration Assistant tool (DMA). DMA 工具输出中没有关于缺失或不兼容功能的错误,可保护应用程序免受新目标版本上的任何功能回归影响。The absence of errors in the DMA tool output, about missing or incompatible functionality, protects application from any functional regressions on the new target version. 有关 DMA 工具的详细信息,请参阅此处For more information on the DMA tool, see here.

备注

DMA 支持数据库兼容性级别 100 及更高级别。DMA supports database compatibility level 100 and above. 排除 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 作为源版本。SQL Server 2005 (9.x)SQL Server 2005 (9.x) as source version is excluded.

重要

Microsoft 建议执行一些最小测试来验证更新是否成功,同时维持之前的数据库兼容性级别。Microsoft recommends that some minimal testing is done to validate the success of an upgrade, while maintaining the previous database compatibility level. 应该确定适用于自己的应用程序和场景的最小测试。You should determine what minimal testing means for your own application and scenario.

备注

Microsoft 在下列情况下提供查询计划形状保护:Microsoft provides query plan shape protection when:

  • 新版 SQL ServerSQL Server(目标)在相当于之前 SQL ServerSQL Server 版本(源)运行的硬件上运行。The new SQL ServerSQL Server version (target) runs on hardware that is comparable to the hardware where the previous SQL ServerSQL Server version (source) was running.
  • 目标 SQL ServerSQL Server 和源 SQL ServerSQL Server 均使用同一支持的数据库兼容性级别The same supported database compatibility level is used both at the target SQL ServerSQL Server and source SQL ServerSQL Server.

上述情况下发生的任何查询计划形状回归(与源 SQL ServerSQL Server 相比)将得到解决。Any query plan shape regression (as compared to the source SQL ServerSQL Server) that occurs in the above conditions will be addressed. 如果出现这种情况,请与 Microsoft 客户支持服务部门联系。Please contact Microsoft Customer Support if this is the case.

利用兼容性级别获得向后兼容Using Compatibility Level for Backward Compatibility

数据库兼容性级别设置只影响指定数据库的行为,而不影响整个服务器的行为 。The database compatibility level setting affects behaviors only for the specified database, not for the entire server. 在与 Transact-SQLTransact-SQL 和查询优化行为相关方面,数据库兼容性级别提供与旧版 SQL ServerSQL Server 的后向兼容性。Database compatibility level provides backward compatibility with earlier versions of SQL ServerSQL Server in what relates to Transact-SQLTransact-SQL and query optimization behaviors.

提示

因为数据库兼容级别 是数据库级设置,所以在较新的 SQL Server 数据库引擎SQL Server Database Engine 上运行但使用较旧的数据库兼容级别的应用程序仍可利用服务器级增强功能,无需对应用程序进行任何更改。Because database compatibility level is a database-level setting, an application running on a newer SQL Server 数据库引擎SQL Server Database Engine while using an older database compatibility level, can still leverage server-level enhancements without any requirement for application changes.

其中包括丰富的监控和故障排除改进,并提供新的系统动态管理视图扩展事件These include rich monitoring and troubleshooting improvements, with new System Dynamic Management Views and Extended Events. 此外,还提升了可伸缩性,例如,提供自动 Soft-NUMAAnd also improved scalability, for example with Automatic Soft-NUMA .

从兼容性模式 130 开始,任何影响功能的新查询计划都有意仅添加到新兼容性级别中。Starting with compatibility mode 130, any new query plan affecting features have been intentionally added only to the new compatibility level. 这样做是为了最大限度地减少在升级过程中由于以下原因而引发的风险:新查询优化行为可能引入的查询计划更改导致性能降低。This has been done in order to minimize the risk during upgrades that arise from performance degradation due to query plan changes potentially introduced by new query optimization behaviors.
从应用程序的角度来看,目标仍应是在某个时间点升级到最新兼容性级别,以继承智能查询处理等一些新功能,区别在于采用可控方式实现此目标。From an application perspective, the goal should still be to upgrade to the latest compatibility level at some point in time, in order to inherit some of the new features such as Intelligent Query Processing, but to do so in a controlled way. 通过将较低兼容性级别用作更安全的迁移辅助工具,可解决相关兼容性级别设置控制的行为之间存在的版本差异问题。Use the lower compatibility level as a safer migration aid to work around version differences, in the behaviors that are controlled by the relevant compatibility level setting. 有关更多详细信息,包括升级数据库兼容性级别的建议工作流,请参阅后文的升级数据库兼容性级别的最佳做法For more details, including the recommended workflow for upgrading database compatibility level, see the Best Practices for upgrading Database Compatibility Level later in the article.

重要

给定 SQL ServerSQL Server 版本中引入的废止功能不受兼容级别保护。Discontinued functionality introduced in a given SQL ServerSQL Server version is not protected by compatibility level. 这指的是从 SQL Server 数据库引擎SQL Server Database Engine 中删除的功能。This refers to functionality that was removed from the SQL Server 数据库引擎SQL Server Database Engine. 例如,FASTFIRSTROW 提示在 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中废止,并替换为 OPTION (FAST n ) 提示。For example, the FASTFIRSTROW hint was discontinued in SQL Server 2012 (11.x)SQL Server 2012 (11.x) and replaced with the OPTION (FAST n ) hint. 将数据库兼容级别设置为 110 不会恢复废止的提示。Setting the database compatibility level to 110 will not restore the discontinued hint.

若要详细了解已中断的功能,请参阅 SQL Server 2016 中已中断的数据库引擎功能SQL Server 2014 中已中断的数据库引擎功能SQL Server 2012 中已中断的数据库引擎功能For more information on discontinued functionality, see Discontinued Database Engine Functionality in SQL Server 2016, Discontinued Database Engine Functionality in SQL Server 2014, and Discontinued Database Engine Functionality in SQL Server 2012.

重要

给定 SQL ServerSQL Server版 本中引入的重大更改可能不受兼容级别保护。Breaking changes introduced in a given SQL ServerSQL Server version may not be protected by compatibility level. 这指的是 SQL Server 数据库引擎SQL Server Database Engine 版本之间的行为变更。This refers to behavior changes between versions of the SQL Server 数据库引擎SQL Server Database Engine. Transact-SQLTransact-SQL 行为通常受兼容级别保护。behavior is usually protected by compatibility level. 但是,已更改或删除的系统对象受兼容级别保护。However, changed or removed system objects are not protected by compatibility level.

受兼容级别保护的一个重大更改示例是从 datetime 到 datetime2 数据类型的隐式转换。An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. 在数据库兼容级别 130 以下,通过考虑导致不同转换值的毫秒小数部分,这些转换显得更加准确。Under database compatibility level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. 若要还原以前的转换行为,请将数据库兼容级别设置为 120 或更低。To restore previous conversion behavior, set the database compatibility level to 120 or lower.

兼容级别不保护的重大更改示例有:Examples of breaking changes not protected by compatibility level are:

  • 系统对象中更改了列名。Changed column names in system objects. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中,sys.dm_os_sys_info 中的列 single_pages_kb 已重命名为 pages_kb 。In SQL Server 2012 (11.x)SQL Server 2012 (11.x) the column single_pages_kb in sys.dm_os_sys_info was renamed to pages_kb. 无论兼容级别如何,查询 SELECT single_pages_kb FROM sys.dm_os_sys_info 都会生成错误 207(列名无效)。Regardless of the compatibility level, the query SELECT single_pages_kb FROM sys.dm_os_sys_info will produce error 207 (Invalid column name).
  • 删除了系统对象。Removed system objects. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中,sp_dboption 已删除。In SQL Server 2012 (11.x)SQL Server 2012 (11.x) the sp_dboption was removed. 无论兼容级别如何,语句 EXEC sp_dboption 'AdventureWorks2016', 'autoshrink', 'FALSE'; 都会生成错误 2812(找不到存储过程“sp_dboption”)。Regardless of the compatibility level, the statement EXEC sp_dboption 'AdventureWorks2016', 'autoshrink', 'FALSE'; will produce error 2812 (Could not find stored procedure 'sp_dboption').

若要详细了解重大更改,请参阅 SQL Server 2017 中的数据库引擎功能重大更改SQL Server 2016 中的数据库引擎功能重大更改SQL Server 2014 中的数据库引擎功能重大更改SQL Server 2012 中的数据库引擎功能重大更改For more information on breaking changes, see Breaking Changes to Database Engine Features in SQL Server 2017, Breaking Changes to Database Engine Features in SQL Server 2016, Breaking Changes to Database Engine Features in SQL Server 2014, and Breaking Changes to Database Engine Features in SQL Server 2012.

升级数据库兼容性级别的最佳做法Best Practices for upgrading Database Compatibility Level

有关用于升级兼容级别的建议工作流,请参阅更改数据库兼容性模式和使用查询存储For the recommended workflow for upgrading the compatibility level, see Change the Database Compatibility Mode and Use the Query Store. 此外,有关升级数据库兼容性级别的协助体验,请参阅使用查询优化助手升级数据库Additionally, for an assisted experience with upgrading the database compatibility level, see Upgrading Databases by using the Query Tuning Assistant.

兼容性级别和存储过程Compatibility Levels and Stored Procedures

执行某一存储过程时,该存储过程将使用定义它的数据库的当前兼容性级别。When a stored procedure executes, it uses the current compatibility level of the database in which it is defined. 在更改某一数据库的兼容性设置时,该数据库的所有存储过程都将随之自动重新编写。When the compatibility setting of a database is changed, all of its stored procedures are automatically recompiled accordingly.

兼容性级别 140 和 150 的区别Differences Between Compatibility Level 140 and Level 150

此部分介绍了随兼容性级别 150 一起引入的新行为。This section describes new behaviors introduced with compatibility level 150.

对于 Azure SQL DatabaseAzure SQL DatabaseSQL Server 2019SQL Server 2019,数据库兼容性级别 150 目前为公共预览版。Database compatibility level 150 is currently in Public Preview for Azure SQL DatabaseAzure SQL Database and SQL Server 2019SQL Server 2019. 除了数据库兼容性级别 140 中引入的改进之外,此数据库兼容性级别还将与下一代查询处理改进相关联。This database compatibility level will be associated with the next generation of query processing improvements beyond what was introduced in database compatibility level 140.

兼容性级别设置为 140 或更低Compatibility-level setting of 140 or lower 兼容性级别设置为 150Compatibility-level setting of 150
关系数据仓库和分析工作负荷可能由于 OLTP 开销、缺少供应商支持或其他限制而无法利用列存储索引。Relational data warehouse and analytic workloads may not be able to leverage columnstore indexes due to OLTP-overhead, lack of vendor support or other limitations. 如果没有列存储索引,这些工作负荷将不能受益于批处理执行模式。Without columnstore indexes, these workloads cannot benefit from batch execution mode. 批处理执行模式现在适用于分析工作负荷,而无需列存储索引。Batch execution mode is now available for analytic workloads without requiring columnstore indexes. 有关详细信息,请参阅行存储上的批处理模式For more information, see batch mode on rowstore.
请求会导致溢出到磁盘的不充足内存授予大小的行模式查询可能会继续对连续执行产生问题。Row-mode queries that request insufficient memory grant sizes that result in spills to disk may continue to have issues on consecutive executions. 请求会导致溢出到磁盘的不充足内存授予大小的行模式查询可能会提高连续执行的性能。Row-mode queries that request insufficient memory grant sizes that result in spills to disk may have improved performance on consecutive executions. 有关详细信息,请参阅行模式内存授予反馈For more information, see row mode memory grant feedback.
请求会导致并发问题的过多内存授予大小的行模式查询可能会继续对连续执行产生问题。Row-mode queries that request an excessive memory grant size that results in concurrency issues may continue to have issues on consecutive executions. 请求会导致并发问题的过多内存授予大小的行模式查询可能会改进连续执行的并发性。Row-mode queries that request an excessive memory grant size that results in concurrency issues may have improved concurrency on consecutive executions. 有关详细信息,请参阅行模式内存授予反馈For more information, see row mode memory grant feedback.
引用 T-SQL 标量 UDF 的查询将使用迭代调用、缺乏成本计算并强制串行执行。Queries referencing T-SQL scalar UDFs will use iterative invocation, lack costing and force serial execution. T-SQL 标量 UDF 将转换为内联在调用查询中的等效关系表达式,这通常会使性能显著提升。T-SQL scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains. 有关详细信息,请参阅 T-SQL 标量 UDF 内联For more information, see T-SQL scalar UDF inlining.
表变量使用固定猜测值来进行基数估计。Table variables use a fixed guess for the cardinality estimate. 如果实际行数远高于猜测值,则下游操作的性能可能会受到影响。If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. 新计划将使用在首次编译时遇到的表变量的实际基数,而不是一个固定猜测值。New plans will use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess. 有关详细信息,请参阅表变量延迟编译For more information, see table variable deferred compilation.

有关数据库兼容性级别 150 中启用的查询处理功能的详细信息,请参阅 SQL Server 2019 中的新增功能SQL 数据库中的智能查询处理For more information on query processing features enabled in database compatibility level 150, refer to What's new in SQL Server 2019 and Intelligent query processing in SQL databases.

兼容级别 130 与兼容级别 140 之间的差异Differences Between Compatibility Level 130 and Level 140

本节介绍随兼容级别 140 引入的新行为。This section describes new behaviors introduced with compatibility level 140.

兼容级别设置为 130 或更低Compatibility-level setting of 130 or lower 兼容级别设置为 140Compatibility-level setting of 140
引用多语句表值函数的语句的基数估计使用固定行猜测。Cardinality estimates for statements referencing multi-statement table-valued functions use a fixed row guess. 引用多语句表值函数的符合条件语句的基数估计会使用函数输出的实际基数。Cardinality estimates for eligible statements referencing multi-statement table-valued functions will use the actual cardinality of the function output. 这通过多语句表值函数的交错执行 来实现。This is enabled via interleaved execution for multi-statement table-valued functions.
请求会导致溢出到磁盘的不充足内存授予大小的批处理模式查询可能会继续对连续执行产生问题。Batch-mode queries that request insufficient memory grant sizes that result in spills to disk may continue to have issues on consecutive executions. 请求会导致溢出到磁盘的不充足内存授予大小的批处理模式查询可能会提高连续执行的性能。Batch-mode queries that request insufficient memory grant sizes that result in spills to disk may have improved performance on consecutive executions. 这通过在对批处理模式运算符发生溢出时,会更新缓存计划内存授予大小的批处理模式内存授予反馈来实现。This is enabled via batch mode memory grant feedback which will update the memory grant size of a cached plan if spills have occurred for batch mode operators.
请求会导致并发问题的过多内存授予大小的批处理模式查询可能会继续对连续执行产生问题。Batch-mode queries that request an excessive memory grant size that results in concurrency issues may continue to have issues on consecutive executions. 请求会导致并发问题的过多内存授予大小的批处理模式查询可能会改进连续执行的并发性。Batch-mode queries that request an excessive memory grant size that results in concurrency issues may have improved concurrency on consecutive executions. 这通过在最初请求了过多量时,会更新缓存计划内存授予大小的批处理模式内存授予反馈来实现。This is enabled via batch mode memory grant feedback which will update the memory grant size of a cached plan if an excessive amount was originally requested.
包含联接运算符的批处理模式查询有资格使用三种物理联接算法,包括嵌套循环、哈希联接和合并联接。Batch-mode queries that contain join operators are eligible for three physical join algorithms, including nested loop, hash join and merge join. 如果基数估计对于联接输入不正确,则可能会选择不适当的联接算法。If cardinality estimates are incorrect for join inputs, an inappropriate join algorithm may be selected. 如果发生这种情况,性能会降低,并继续使用不适当的联接算法,直到缓存计划进行重新编译。If this occurs, performance will suffer and the inappropriate join algorithm will remain in use until the cached plan is recompiled. 有一个名为自适应联接的其他联接运算符。There is an additional join operator called adaptive join. 如果基数估计对于外部生成联接输入不正确,则可能会选择不适当的联接算法。If cardinality estimates are incorrect for the outer build join input, an inappropriate join algorithm may be selected. 如果发生这种情况,并且语句有资格进行自适应联接,则会将嵌套循环用于较小联接输入,将哈希联接动态用于较大联接输入,而无需重新编译。If this occurs and the statement is eligible for an adaptive join, a nested loop will be used for smaller join inputs and a hash join will be used for larger join inputs dynamically without requiring recompilation.
引用列存储索引的普通计划没有资格进行批处理模式执行。Trivial plans referencing Columnstore indexes are not eligible for batch mode execution. 引用列存储索引的普通计划会被放弃,以便支持有条件进行批处理模式执行的计划。A trivial plan referencing Columnstore indexes will be discarded in favor of a plan that is eligible for batch mode execution.
sp_execute_external_script UDX 运算符只能在行模式下运行。The sp_execute_external_script UDX operator can only run in row mode. sp_execute_external_script UDX 运算符有资格进行批处理模式执行。The sp_execute_external_script UDX operator is eligible for batch mode execution.
多语句表值函数 (TVF) 没有交错执行Multi-statement table-valued functions (TVF's) do not have interleaved execution 用于改进计划质量的多语句 TVF 交错执行。Interleaved execution for multi-statement TVFs to improve plan quality.

SQL Server 2017 之前的早期 SQL Server 版本中处于跟踪标志 4199 下的修补程序现在默认情况下会启用。Fixes that were under trace flag 4199 in earlier versions of SQL Server prior to SQL Server 2017 are now enabled by default. 具有兼容性模式 140。With compatibility mode 140. 跟踪标志 4199 仍会适用于在 SQL Server 2017 之后发布的新查询优化器修补程序。Trace flag 4199 will still be applicable for new query optimizer fixes that are released after SQL Server 2017. 有关跟踪标志 4199 的信息,请参阅跟踪标志 4199For information about Trace Flag 4199, see Trace Flag 4199.

兼容级别 120 和兼容级别 130 之间的差异Differences Between Compatibility Level 120 and Level 130

本节介绍随兼容级别 130 引入的新行为。This section describes new behaviors introduced with compatibility level 130.

兼容级别设置为 120 或更低Compatibility-level setting of 120 or lower 兼容级别设置为 130Compatibility-level setting of 130
INSERT-SELECT 语句中的 INSERT 是单线程。The INSERT in an INSERT-SELECT statement is single-threaded. INSERT-SELECT 语句中的 INSERT 是多线程,或者可以具有并行计划。The INSERT in an INSERT-SELECT statement is multi-threaded or can have a parallel plan.
内存优化表的查询执行单线程。Queries on a memory-optimized table execute single-threaded. 内存优化表的查询现在可以具有并行计划。Queries on a memory-optimized table can now have parallel plans.
引入了 SQL 2014 基数估算器 CardinalityEstimationModelVersion="120"Introduced the SQL 2014 Cardinality estimator CardinalityEstimationModelVersion="120" 基数估计模型 130 带来了进一步基数估计 (CE) 改进(在查询计划中可见)。Further cardinality estimation (CE) Improvements with the Cardinality Estimation Model 130 which is visible from a Query plan. CardinalityEstimationModelVersion="130"CardinalityEstimationModelVersion="130"
列存储索引的批处理模式与行模式更改:Batch mode versus Row Mode changes with Columnstore indexes:
  • 具有列存储索引的表上的排序处于行模式Sorts on a table with Columnstore index are in Row mode
  • 开窗函数聚合在行模式(如 LAGLEAD)下运行Windowing function aggregates operate in row mode such as LAG or LEAD
  • 具有多个不同子句的列存储表的查询在行模式下运行Queries on Columnstore tables with Multiple distinct clauses operated in Row mode
  • 在 MAXDOP 1 下运行或具有串行计划的查询在行模式下执行Queries running under MAXDOP 1 or with a serial plan executed in Row mode
列存储索引的批处理模式与行模式更改:Batch mode versus Row Mode changes with Columnstore indexes:
  • 具有列存储索引的表上的排序现在处于批处理模式Sorts on a table with a Columnstore index are now in batch mode
  • 开窗聚合现在在批处理模式(如 LAGLEAD)下运行Windowing aggregates now operate in batch mode such as LAG or LEAD
  • 具有多个不同子句的列存储表的查询在批处理模式下运行Queries on Columnstore tables with Multiple distinct clauses operate in Batch mode
  • 在 MAXDOP 1 下运行或具有串行计划的查询在批处理模式下执行Queries running under MAXDOP 1 or with a serial plan execute in Batch Mode
可以自动更新统计信息。Statistics can be automatically updated. 自动更新统计信息的逻辑对大型表更主动。The logic which automatically updates statistics is more aggressive on large tables. 在实践中,这应减少以下情况:对于经常查询新插入的行,但是未更新统计信息以包括这些值的查询,客户遇到性能问题。In practice, this should reduce cases where customers have seen performance issues on queries where newly inserted rows are queried frequently but where the statistics had not been updated to include those values.
SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,跟踪 2371 默认情况下会关闭。Trace 2371 is OFF by default in SQL Server 2014 (12.x)SQL Server 2014 (12.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中,Trace 2371(跟踪 2371)默认情况下会打开。Trace 2371 is ON by default in SQL Server 2016 (13.x)SQL Server 2016 (13.x). 跟踪标志 2371 告知自动统计信息更新程序在具有许多行的表中采样更小但更智能的行子集。Trace flag 2371 tells the auto statistics updater to sample a smaller yet wiser subset of rows, in a table that has a great many rows.

一个重要改进是在采样中包括更多最近插入的行。One improvement is to include in the sample more rows that were inserted recently.

另一个改进是让查询在更新统计信息进程运行期间运行,而不阻塞查询。Another improvement is to let queries run while the update statistics process is running, rather than blocking the query.
对于级别 120,统计信息通过单 线程进程进行采样。For level 120, statistics are sampled by a single-threaded process. 对于级别 130,统计信息通过多 线程进程进行采样。For level 130, statistics are sampled by a multi-threaded process.
253 传入外键是限制。253 incoming foreign keys is the limit. 给定表可以通过最多 10,000 个传入外键或类似引用进行引用。A given table can be referenced by up to 10,000 incoming foreign keys or similar references. 有关限制,请参阅 Create Foreign Key RelationshipsFor restrictions, see Create Foreign Key Relationships.
允许使用弃用的 MD2、MD4、MD5、SHA 和 SHA1 哈希算法。The deprecated MD2, MD4, MD5, SHA, and SHA1 hash algorithms are permitted. 只允许使用 SHA2_256 和 SHA2_512 哈希算法。Only SHA2_256 and SHA2_512 hash algorithms are permitted.
SQL Server 2016 (13.x)SQL Server 2016 (13.x) 包括对某些数据类型转换和某些不常见操作的改进。includes improvements in some data types conversions and some (mostly uncommon) operations. 有关详细信息,请参阅 SQL Server 2016 improvements in handling some data types and uncommon operations(SQL Server 2016 在处理某些数据类型和不常见操作方面所做的改进)For details see SQL Server 2016 improvements in handling some data types and uncommon operations.
STRING_SPLIT 函数不可用。The STRING_SPLIT function is not available. STRING_SPLIT 函数在兼容性级别 130 或更高级别下可用。The STRING_SPLIT function is available under compatibility level 130 or above. 如果数据库兼容性级别低于 130,SQL ServerSQL Server 将无法找到并执行 STRING_SPLIT 函数。If your database compatibility level is lower than 130, SQL ServerSQL Server will not be able to find and execute STRING_SPLIT function.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 之前的早期 SQL ServerSQL Server 版本中处于跟踪标志 4199 下的修补程序现在默认情况下会启用。Fixes that were under trace flag 4199 in earlier versions of SQL ServerSQL Server prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) are now enabled by default. 具有兼容性模式 130。With compatibility mode 130. 跟踪标志 4199 仍会适用于在 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 之后发布的新查询优化器修补程序。Trace flag 4199 will still be applicable for new query optimizer fixes that are released after SQL Server 2016 (13.x)SQL Server 2016 (13.x). 若要在 SQL 数据库SQL Database中使用较旧的查询优化器,必须选择兼容级别 110。To use the older query optimizer in SQL 数据库SQL Database you must select compatibility level 110. 有关跟踪标志 4199 的信息,请参阅跟踪标志 4199For information about Trace Flag 4199, see Trace Flag 4199.

较低兼容性级别和级别 120 之间的差异Differences Between Lower Compatibility Levels and Level 120

本节介绍随兼容性级别 120 引入的新行为。This section describes new behaviors introduced with compatibility level 120.

兼容性级别设置为 110 或更低Compatibility-level setting of 110 or lower 兼容性级别设置为 120Compatibility-level setting of 120
使用旧版查询优化器。The older query optimizer is used. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 包括对创建和优化查询计划的组件的显著改进。includes substantial improvements to the component that creates and optimizes query plans. 这个新的查询优化器功能依赖于使用数据库兼容性级别 120。This new query optimizer feature is dependent upon use of the database compatibility level 120. 若要利用这些改进,应使用数据库兼容性级别 120 开发新的数据库应用程序。New database applications should be developed using database compatibility level 120 to take advantage of these improvements. 应对从较早版本的 SQL ServerSQL Server 中迁移的应用程序进行仔细测试,以便确认保持或改进了好的性能。Applications that are migrated from earlier versions of SQL ServerSQL Server should be carefully tested to confirm that good performance is maintained or improved. 如果性能下降,可以将数据库兼容性级别设置为 110 或更低,以便使用较早的查询优化器方法。If performance degrades, you can set the database compatibility level to 110 or earlier to use the older query optimizer methodology.

数据库兼容级别 120 使用针对现代数据仓库和 OLTP 工作负荷进行优化的新基数估计器。Database compatibility level 120 uses a new cardinality estimator that is tuned for modern data warehousing and OLTP workloads. 在因为性能问题将数据库兼容级别设置为 110 前,请参阅 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 数据库引擎中的新增功能主题的“查询计划”一节中的建议。Before setting database compatibility level to 110 because of performance issues, see the recommendations in the Query Plans section of the SQL Server 2014 (12.x)SQL Server 2014 (12.x) What's New in Database Engine topic.
如果兼容级别低于 120,则在将 date 值转换为字符串值时语言设置将被忽略。In compatibility levels lower than 120, the language setting is ignored when converting a date value to a string value. 请注意,此行为仅特定于 date 类型。Note that this behavior is specific only to the date type. 请参阅下面“示例”部分中的“示例 B”。See example B in the Examples section below. date 值转换为字符串值时,不忽略语言设置。The language setting is not ignored when converting a date value to a string value.
EXCEPT 子句右侧的递归引用产生无限循环。Recursive references on the right-hand side of an EXCEPT clause create an infinite loop. 下面“示例”部分中的示例 C 演示此行为。Example C in the Examples section below demonstrates this behavior. EXCEPT 子句中的递归引用产生遵从 ANSI SQL 标准的错误。Recursive references in an EXCEPT clause generates an error in compliance with the ANSI SQL standard.
递归公用表表达式 (CTE) 允许重复的列名。Recursive common table expression (CTE) allows duplicate column names. 递归 CTE 禁止列名重复。Recursive CTE does not allow duplicate column names.
如果更改触发器,则启用禁用的触发器。Disabled triggers are enabled if the triggers are altered. 更改触发器不更改触发器的状态(已启用或已禁用)。Altering a trigger does not change the state (enabled or disabled) of the trigger.
OUTPUT INTO 表子句忽略 IDENTITY_INSERT SETTING = OFF,并允许插入显式值。The OUTPUT INTO table clause ignores the IDENTITY_INSERT SETTING = OFF and allows explicit values to be inserted. IDENTITY_INSERT 设置为 OFF 后,不能为表中的标识列插入显式值。You cannot insert explicit values for an identity column in a table when IDENTITY_INSERT is set to OFF.
将数据库包含设置为部分包含后,验证 MERGE 语句的 OUTPUT 子句中的 $action 字段可能会返回排序规则错误。When the database containment is set to partial, validating the $action field in the OUTPUT clause of a MERGE statement can return a collation error. MERGE 语句的 $action 子句返回的值的排序规则是数据库排序规则而非服务器排序规则,因此不会返回排序规则冲突错误。The collation of the values returned by the $action clause of a MERGE statement is the database collation instead of the server collation and a collation conflict error is not returned.
SELECT INTO 语句始终创建单线程插入操作。A SELECT INTO statement always creates a single-threaded insert operation. SELECT INTO 语句可创建并行插入操作。A SELECT INTO statement can create a parallel insert operation. 插入大量行时,并行操作可能会提升性能。When inserting a large number of rows, the parallel operation can improve performance.

低兼容性级别与级别 100 和 110 的区别Differences Between Lower Compatibility Levels and Levels 100 and 110

本节介绍随兼容性级别 110 引入的新行为。This section describes new behaviors introduced with compatibility level 110. 此部分还适用于大于 110 的兼容性级别。This section also applies to compatibility levels above 110.

兼容性级别设置为 100 或更低Compatibility-level setting of 100 or lower 至少为 110 的兼容性级别设置Compatibility-level setting of at least 110
公共语言运行时 (CLR) 数据库对象用 CLR 的版本 4 执行。Common language runtime (CLR) database objects are executed with version 4 of the CLR. 但会避免在 CLR 的版本 4 中引入的某些行为更改。However, some behavior changes introduced in version 4 of the CLR are avoided. 有关详细信息,请参阅 CLR 集成中的新增功能For more information, see What's New in CLR Integration. CLR 数据库对象用 CLR 的版本 4 执行。CLR database objects are executed with version 4 of the CLR.
XQuery 函数 string-lengthsubstring 将每个代理项计为两个字符。The XQuery functions string-length and substring count each surrogate as two characters. XQuery 函数 string-lengthsubstring 将每个代理项计为一个字符。The XQuery functions string-length and substring count each surrogate as one character.
在递归公用表表达式 (CTE) 查询中允许 PIVOTPIVOT is allowed in a recursive common table expression (CTE) query. 然而,当每个分组有多个行时,该查询返回不正确的结果。However, the query returns incorrect results when there are multiple rows per grouping. 在递归公用表表达式 (CTE) 查询中不允许 PIVOTPIVOT is not allowed in a recursive common table expression (CTE) query. 将返回错误。An error is returned.
RC4 算法仅用于支持向后兼容性。The RC4 algorithm is only supported for backward compatibility. 仅当数据库兼容级别为 90 或 100 时,才能使用 RC4 或 RC4_128 对新材料进行加密。New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (建议不要使用。)在 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中,可以通过任何兼容性级别对使用 RC4 或 RC4_128 加密的材料进行解密。(Not recommended.) In SQL Server 2012 (11.x)SQL Server 2012 (11.x), material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level. 不能使用 RC4 或 RC4_128 加密新材料。New material cannot be encrypted using RC4 or RC4_128. 而是使用一种较新的算法,如 AES 算法之一。Use a newer algorithm such as one of the AES algorithms instead. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中,可以通过任何兼容性级别对使用 RC4 或 RC4_128 加密的材料进行解密。In SQL Server 2012 (11.x)SQL Server 2012 (11.x), material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
timedatetime2 数据类型的 CASTCONVERT 操作的默认样式为 121,当在计算列表达式中使用这些类型时除外。The default style for CAST and CONVERT operations on time and datetime2 data types is 121 except when either type is used in a computed column expression. 对于计算列,默认样式为 0。For computed columns, the default style is 0. 当创建用于涉及自动参数化的查询中或约束定义中的计算列时,此行为会影响计算列。This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

下面“示例”部分中的示例 D 显示样式 0 与 121 之间的差异。Example D in the Examples section below shows the difference between styles 0 and 121. 它并不演示上面所述的行为。It does not demonstrate the behavior described above. 有关日期和时间样式的详细信息,请参阅 CAST 和 CONVERTFor more information about date and time styles, see CAST and CONVERT.
兼容级别为 110 时,对 timedatetime2 数据类型的 CASTCONVERT 操作的默认样式始终为 121。Under compatibility level 110, the default style for CAST and CONVERT operations on time and datetime2 data types is always 121. 如果您的查询依赖旧行为,请使用低于 110 的兼容性级别或在受影响的查询中显式指定 0 样式。If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

将数据库升级到兼容性级别 110 将不更改已存储到磁盘的用户数据。Upgrading the database to compatibility level 110 will not change user data that has been stored to disk. 您必须相应手动更正此数据。You must manually correct this data as appropriate. 例如,如果使用了 SELECT INTO 来从包含上述计算列表达式的源创建表,将存储数据(使用样式 0)而非存储计算列定义本身。For example, if you used SELECT INTO to create a table from a source that contained a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. 您需要手动更新此数据,以匹配样式 121。You would need to manually update this data to match style 121.
在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 datetimeAny columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as datetime. 本地表中相应的列(在选择列表中的相同序号位置中)必须为 datetime 类型。Corresponding columns in local tables (in the same ordinal position in the select list) must be of type datetime. 在分区视图中引用的远程表的所有 smalldatetime 类型的列都将映射为 smalldatetimeAny columns in remote tables of type smalldatetime that are referenced in a partitioned view are mapped as smalldatetime. 本地表中相应的列(在选择列表中的相同序号位置中)必须为 smalldatetime 类型。Corresponding columns in local tables (in the same ordinal position in the select list) must be of type smalldatetime.

在升级到 110 后,分布式分区视图将由于数据类型不匹配而失败。After upgrading to 110, the distributed partitioned view will fail because of the data type mismatch. 可以通过将针对远程表的数据类型更改为 datetime 或者将本地数据库的兼容级别设置为 100 或更低,解决上述问题。You can resolve this by changing the data type on the remote table to datetime or setting the compatibility level of the local database to 100 or lower.
SOUNDEX 函数实现以下规则:SOUNDEX function implements the following rules:

1) 当分隔两个具有相同 SOUNDEX 代码的辅音时,将忽略大写 H 或大写 W。1) Upper-case H or upper-case W are ignored when separating two consonants that have the same number in the SOUNDEX code.

2) 如果 character_expression 的前 2 个字符具有相同的 SOUNDEX 代码,则将包含这两个字符。2) If the first 2 characters of character_expression have the same number in the SOUNDEX code, both characters are included. 否则,如果一组并行辅音在 SOUNDEX 代码中有相同的数字,所有这些辅音都会被排除在外,第一个辅音除外。Else, if a set of side-by-side consonants have the same number in the SOUNDEX code, all of them are excluded except the first.
SOUNDEX 函数实现以下规则:SOUNDEX function implements the following rules:

1) 如果大写 H 或大写 W 分隔具有相同 SOUNDEX 代码的两个辅音,则将忽略右侧的辅音1) If upper-case H or upper-case W separate two consonants that have the same number in the SOUNDEX code, the consonant to the right is ignored

2) 如果一组并行辅音在 SOUNDEX 代码中有相同的数字,所有这些辅音都会被排除在外,第一个辅音除外。2) If a set of side-by-side consonants have the same number in the SOUNDEX code, all of them are excluded except the first.



其他规则可能导致由 SOUNDEX 函数计算的值不同于在更低数据库兼容级别时计算的值。The additional rules may cause the values computed by the SOUNDEX function to be different than the values computed under earlier compatibility levels. 在升级到兼容级别 110 后,可能需要重新生成使用 SOUNDEX 函数的索引、堆或 CHECK 约束。After upgrading to compatibility level 110, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function. 有关详细信息,请参阅 SOUNDEXFor more information, see SOUNDEX.

兼容性级别 90 和兼容性级别 100 之间的差异Differences Between Compatibility Level 90 and Level 100

本节介绍随兼容性级别 100 引入的新行为。This section describes new behaviors introduced with compatibility level 100.

兼容性级别设置为 90Compatibility-level setting of 90 兼容性级别设置为 100Compatibility-level setting of 100 影响的可能性Possibility of impact
对于多语句表值函数,在创建它们时,无论会话级别设置如何,QUOTED_IDENTIFER 设置始终为 ON。The QUOTED_IDENTIFER setting is always set to ON for multistatement table-valued functions when they are created regardless of the session level setting. 在创建多语句表值函数时,会遵循 QUOTED IDENTIFIER 会话设置。The QUOTED IDENTIFIER session setting is honored when multistatement table-valued functions are created. MediumMedium
在创建或更改分区函数时,会评估函数中的 datetimesmalldatetime 文字,并假定语言设置为 US_English。When you create or alter a partition function, datetime and smalldatetime literals in the function are evaluated assuming US_English as the language setting. 使用当前语言设置来评估该分区函数中的 datetimesmalldatetime 文字。The current language setting is used to evaluate datetime and smalldatetime literals in the partition function. MediumMedium
允许在 INSERTSELECT INTO 语句中使用(并忽略)FOR BROWSE 子句。The FOR BROWSE clause is allowed (and ignored) in INSERT and SELECT INTO statements. 不允许在 INSERTSELECT INTO 语句中使用 FOR BROWSE 子句。The FOR BROWSE clause is not allowed in INSERT and SELECT INTO statements. MediumMedium
OUTPUT 子句中允许使用全文谓词。Full-text predicates are allowed in the OUTPUT clause. OUTPUT 子句中不允许使用全文谓词。Full-text predicates are not allowed in the OUTPUT clause. LowLow
CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTDROP FULLTEXT STOPLIST 不受支持。CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are not supported. 系统非索引字表自动与新的全文检索相关联。The system stoplist is automatically associated with new full-text indexes. CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTDROP FULLTEXT STOPLIST 受支持。CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported. LowLow
MERGE 不作为保留关键字强制应用。MERGE is not enforced as a reserved keyword. MERGE 是完全保留的关键字。MERGE is a fully reserved keyword. 在 100 和 90 兼容级别下,都支持 MERGE 语句。The MERGE statement is supported under both 100 and 90 compatibility levels. LowLow
使用 INSERT 语句的 <dml_table_source> 参数会引发语法错误。Using the <dml_table_source> argument of the INSERT statement raises a syntax error. 您可以捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。You can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. 这通过使用 INSERT 语句的 <dml_table_source> 参数来实现。This is done using the <dml_table_source> argument of the INSERT statement. LowLow
除非指定 NOINDEX,否则 DBCC CHECKDBDBCC CHECKTABLE 将对单个表或索引视图及其所有非聚集索引和 XML 索引同时执行物理和逻辑一致性检查。Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. 不支持空间索引。Spatial indexes are not supported. 除非指定 NOINDEX,否则 DBCC CHECKDBDBCC CHECKTABLE 将对单个表及其所有非聚集索引同时执行物理和逻辑一致性检查。Unless NOINDEX is specified, DBCC CHECKDB or DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. 但是,在默认情况下,仅对 XML 索引、空间索引和索引视图执行物理一致性检查。However, on XML indexes, spatial indexes, and indexed views, only physical consistency checks are performed by default.

如果指定了 WITH EXTENDED_LOGICAL_CHECKS,则将对索引视图、XML 索引和空间索引(如果存在)执行逻辑检查。If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on indexed views, XML indexes, and spatial indexes, where present. 默认情况下,先执行物理一致性检查,然后执行逻辑一致性检查。By default, physical consistency checks are performed before the logical consistency checks. 如果还指定了 NOINDEX,则仅执行逻辑检查。If NOINDEX is also specified, only the logical checks are performed.
LowLow
如果将 OUTPUT 子句和数据操作语言 (DML) 语句一起使用,并且在语句执行过程中发生运行时错误,则会终止并回滚整个事务。When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the entire transaction is terminated and rolled back. 如果将 OUTPUT 子句和数据操作语言 (DML) 语句一起使用,并且在语句执行过程中发生运行时错误,则行为取决于 SET XACT_ABORT 设置。When an OUTPUT clause is used with a data manipulation language (DML) statement and a run-time error occurs during statement execution, the behavior depends on the SET XACT_ABORT setting. 如果 SET XACT_ABORT 设置为 OFF,则由使用 OUTPUT 子句的 DML 语句所生成的语句中止错误将终止该语句,但批处理的执行仍会继续,并且不会回滚事务。If SET XACT_ABORT is OFF, a statement abort error generated by the DML statement using the OUTPUT clause will terminate the statement, but the execution of the batch continues and the transaction is not rolled back. 如果 SET XACT_ABORT 设置为 ON,则由使用 OUTPUT 子句的 DML 语句所生成的全部运行时错误都将终止批处理,并回滚事务。If SET XACT_ABORT is ON, all run-time errors generated by the DML statement using the OUTPUT clause will terminate the batch, and the transaction is rolled back. LowLow
CUBE 和 ROLLUP 不作为保留关键字强制应用。CUBE and ROLLUP are not enforced as reserved keywords. CUBEROLLUP 是 GROUP BY 子句中的保留关键字。CUBE and ROLLUP are reserved keywords within the GROUP BY clause. LowLow
对 XML anyType 类型的元素应用严格验证。Strict validation is applied to elements of the XML anyType type. anyType 类型的元素应用宽松验证。Lax validation is applied to elements of the anyType type. 有关详细信息,请参阅通配符组成部分和内容验证For more information, see Wildcard Components and Content Validation. LowLow
数据操作语言语句不能查询或修改特殊属性 xsi:nilxsi:typeThe special attributes xsi:nil and xsi:type cannot be queried or modified by data manipulation language statements.

这意味着 /e/@xsi:nil 失败,同时 /e/@* 忽略 xsi:nilxsi:type 属性。This means that /e/@xsi:nil fails while /e/@* ignores the xsi:nil and xsi:type attributes. 但是,/e 返回 xsi:nilxsi:type 属性,以保持与 SELECT xmlCol 的一致性,即使 xsi:nil = "false" 也是如此。However, /e returns the xsi:nil and xsi:type attributes for consistency with SELECT xmlCol, even if xsi:nil = "false".
特殊属性 xsi:nilxsi:type 作为常规属性存储,不能查询和修改。The special attributes xsi:nil and xsi:type are stored as regular attributes and can be queried and modified.

例如,执行查询 SELECT x.query('a/b/@*') 会返回包括 xsi: nilxsi: type 在内的所有属性。For example, executing the query SELECT x.query('a/b/@*') returns all attributes including xsi:nil and xsi:type. 若要在查询中排除这些类型,请用 @*[namespace-uri(.) != "insert xsi namespace uri " 替换 @*,而不是用 (local-name(.) = "type"local-name(.) ="nil". 来替换To exclude these types in the query, replace @* with @*[namespace-uri(.) != "insert xsi namespace uri" and not (local-name(.) = "type" or local-name(.) ="nil".
LowLow
用于将 XML 常量字符串值转换为 SQL ServerSQL Server datetime 类型的用户定义函数被标记为确定的。A user-defined function that converts an XML constant string value to a SQL ServerSQL Server datetime type is marked as deterministic. 用于将 XML 常量字符串值转换为 SQL ServerSQL Server datetime 类型的用户定义函数被标记为不确定的。A user-defined function that converts an XML constant string value to a SQL ServerSQL Server datetime type is marked as non-deterministic. LowLow
不完全支持 XML 联合和列表类型。The XML union and list types are not fully supported. 完全支持联合和列表类型,包括以下功能:The union and list types are fully supported including the following functionality:

列表的联合Union of list

联合的联合Union of union

原子类型的列表List of atomic types

联合的列表List of union
LowLow
当视图或内联表值函数中包含 xQuery 方法时,不对该方法所需的 SET 选项进行验证。The SET options required for an xQuery method are not validated when the method is contained in a view or inline table-valued function. 当视图或内联表值函数中包含 xQuery 方法时,对该方法所需的 SET 选项进行验证。The SET options required for an xQuery method are validated when the method is contained in a view or inline table-valued function. 如果该方法的 SET 选项设置不正确,将引发一个错误。An error is raised if the SET options of the method are set incorrectly. LowLow
包含行尾字符(回车符和换行符)的 XML 属性值不根据 XML 标准进行规范化。XML attribute values that contain end-of-line characters (carriage return and line feed) are not normalized according to the XML standard. 即返回回车符和换行符,而不是单个换行符。That is, both characters are returned instead of a single line-feed character. 包含行尾字符(回车符和换行符)的 XML 属性值会根据 XML 标准进行规范化。XML attribute values that contain end-of-line characters (carriage return and line feed) are normalized according to the XML standard. 也就是说,外部已分析实体(包括文档实体)中的所有换行符都会在输入时进行规范化,方法是将两字符序列 #xD #xA 和后面没有跟 #xA 的所有 #xD 都转换为单个 #xA 字符。That is, all line breaks in external parsed entities (including the document entity) are normalized on input by translating both the two-character sequence #xD #xA and any #xD that is not followed by #xA to a single #xA character.

使用属性来传输包含行尾字符的字符串值的应用程序接收到的这些字符将和提交时有所不同。Applications that use attributes to transport string values that contain end-of-line characters will not receive these characters back as they are submitted. 若要避免规范化过程,请使用 XML 数字字符实体对所有行尾字符进行编码。To avoid the normalization process, use the XML numeric character entities to encode all end-of-line characters.
LowLow
ROWGUIDCOLIDENTITY 列属性可能错误地命名为约束。The column properties ROWGUIDCOL and IDENTITY can be incorrectly named as a constraint. 例如,CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) 语句可以执行,但约束名不会保留,也无法让用户访问。For example the statement CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) executes, but the constraint name is not preserved and is not accessible to the user. ROWGUIDCOLIDENTITY 列属性不能命名为约束。The column properties ROWGUIDCOL and IDENTITY cannot be named as a constraint. 返回错误 156。Error 156 is returned. LowLow
使用双向赋值(如 UPDATE T1 SET @v = column_name = <expression>)来更新列会产生意外后果,因为在语句执行过程中,可以在其他子句(如 WHEREON 子句)中使用变量的实时值,而不是使用语句起始值。Updating columns by using a two-way assignment such as UPDATE T1 SET @v = column_name = <expression> can produce unexpected results because the live value of the variable can be used in other clauses such as the WHERE and ON clause during statement execution instead of the statement starting value. 这会导致谓词的含义无法预测地逐行变化。This can cause the meanings of the predicates to change unpredictably on a per-row basis.

只有在兼容性级别设置为 90 时,此行为才适用。This behavior is applicable only when the compatibility level is set to 90.
使用双向赋值来更新列会产生预期的结果,因为在语句执行过程中,只会访问列的语句起始值。Updating columns by using a two-way assignment produces expected results because only the statement starting value of the column is accessed during statement execution. LowLow
请参阅下面“示例”部分中的“示例 E”。See example E in the Examples section below. 请参阅下面“示例”部分中的“示例 F”。See example F in the Examples section below. LowLow
ODBC 函数 {fn CONVERT()} 使用语言的默认日期格式。The ODBC function {fn CONVERT()} uses the default date format of the language. 对于有些语言,默认格式为 YDM,这会导致在将 CONVERT() 与要求使用 YMD 格式的其他函数(如 {fn CURDATE()})结合使用时出现转换错误。For some languages, the default format is YDM, which can result in conversion errors when CONVERT() is combined with other functions, such as {fn CURDATE()}, that expect a YMD format. 在转换为 ODBC 数据类型 SQL_TIMESTAMP、SQL_DATE、SQL_TIME、SQLDATE、SQL_TYPE_TIME 和 SQL_TYPE_TIMESTAMP 时,ODBC 函数 {fn CONVERT()} 使用样式 121(一种独立于语言的 YMD 格式)。The ODBC function {fn CONVERT()} uses style 121 (a language-independent YMD format) when converting to the ODBC data types SQL_TIMESTAMP, SQL_DATE, SQL_TIME, SQLDATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP. LowLow
日期时间内部函数(如 DATEPART)不需要字符串输入值,即可成为有效的日期时间文字。Datetime intrinsics such as DATEPART do not require string input values to be valid datetime literals. 例如,SELECT DATEPART (year, '2007/05-30') 会编译成功。For example, SELECT DATEPART (year, '2007/05-30') compiles successfully. 日期时间内部函数(如 DATEPART)需要字符串输入值,才能成为有效的日期时间文字。Datetime intrinsics such as DATEPART require string input values to be valid datetime literals. 在使用无效的日期时间文字时,会返回错误 241。Error 241 is returned when an invalid datetime literal is used. LowLow

保留关键字Reserved Keywords

兼容性设置还确定了数据库引擎Database Engine所保留的关键字。The compatibility setting also determines the keywords that are reserved by the 数据库引擎Database Engine. 下表显示了每个兼容性级别所引入的保留关键字。The following table shows the reserved keywords that are introduced by each of the compatibility levels.

兼容性级别设置Compatibility-level setting 保留关键字Reserved keywords
130130 待定。To be determined.
120120 无。None.
110110 WITHIN GROUP、TRY_CONVERT、SEMANTICKEYPHRASETABLE、SEMANTICSIMILARITYDETAILSTABLE、SEMANTICSIMILARITYTABLEWITHIN GROUP, TRY_CONVERT, SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE, SEMANTICSIMILARITYTABLE
100100 CUBE、MERGE、ROLLUPCUBE, MERGE, ROLLUP
9090 EXTERNAL、PIVOT、UNPIVOT、REVERT、TABLESAMPLEEXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE

在给定兼容性级别,保留关键字包括在该级别或较低级别引入的所有关键字。At a given compatibility level, the reserved keywords include all of the keywords introduced at or below that level. 例如,对于兼容性级别为 110 的应用程序,将保留上表列出的所有关键字。Thus, for instance, for applications at level 110, all of the keywords listed in the preceding table are reserved. 在较低的兼容性级别中,级别 100 的关键字仍保留有效的对象名,但与这些关键字相对应的级别 110 的语言功能将不可用。At the lower compatibility levels, level-100 keywords remain valid object names, but the level-110 language features corresponding to those keywords are unavailable.

一旦引入,关键字便会保持为保留关键字。Once introduced, a keyword remains reserved. 例如,在兼容性级别 90 中引入的保留关键字 PIVOT 在级别 100、110 和 120 中也被保留。For example, the reserved keyword PIVOT, which was introduced in compatibility level 90, is also reserved in levels 100, 110, and 120.

如果某一应用程序使用对其保留级别而言是关键字的标识符,则该应用程序将失败。If an application uses an identifier that is reserved as a keyword for its compatibility level, the application will fail. 若要解决这一问题,请用方括号 ( [] ) 或引号 ( "" ) 括起该标识符;例如,若要将使用标识符 EXTERNAL 的应用程序升级为兼容级别 90,可以将该标识符更改为 [EXTERNAL]"EXTERNAL"To work around this, enclose the identifier between either brackets ([]) or quotation marks (""); for example, to upgrade an application that uses the identifier EXTERNAL to compatibility level 90, you could change the identifier to either [EXTERNAL] or "EXTERNAL".

有关详细信息,请参阅保留关键字For more information, see Reserved Keywords.

权限Permissions

需要对数据库拥有 ALTER 权限。Requires ALTER permission on the database.

示例Examples

A.A. 更改兼容性级别Changing the compatibility level

以下示例将 AdventureWorks2012AdventureWorks2012 数据库的兼容级别更改为 110,SQL Server 2012 (11.x)SQL Server 2012 (11.x)The following example changes the compatibility level of the AdventureWorks2012AdventureWorks2012 database to 110,SQL Server 2012 (11.x)SQL Server 2012 (11.x).

ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110;
GO

以下示例返回当前数据库的兼容级别。The following example returns the compatibility level of the current database.

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

B.B. 忽略 SET LANGUAGE 语句(除非低于兼容级别 120)Ignoring the SET LANGUAGE statement except under compatibility level 120

只有兼容级别低于 120 时,以下查询才会忽略 SET LANGUAGE 语句。The following query ignores the SET LANGUAGE statement except under compatibility level 120.

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

C.C. 对于 110 或更低的兼容级别设置,EXCEPT 子句右侧的递归引用产生无限循环For compatibility-level setting of 110 or lower, recursive references on the right-hand side of an EXCEPT clause create an infinite loop

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;

D.D. 样式 0 与 121 之间的差异The difference between styles 0 and 121

有关日期和时间样式的详细信息,请参阅 CAST 和 CONVERTFor more information about date and time styles, see CAST and CONVERT.

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

E.E. 变量赋值 - 顶级 UNION 运算符Variable assignment - top-level UNION operator

在包含顶级 UNION 运算符的语句中,允许使用变量赋值,但会返回意外的结果。Variable assignment is allowed in a statement containing a top-level UNION operator, but returns unexpected results. 例如,在以下语句中,将来自两个表的联合的 @v 列的值赋给局部变量 BusinessEntityIDFor example, in the following statements, local variable @v is assigned the value of the column BusinessEntityID from the union of two tables. 按照定义,如果 SELECT 语句返回多个值,则将返回的最后一个值赋给变量。By definition, when the SELECT statement returns more than one value, the variable is assigned the last value that is returned. 在这种情况下,会正确地将最后一个值赋给变量,但还会返回 SELECT UNION 语句的结果集。In this case, the variable is correctly assigned the last value, however, the result set of the SELECT UNION statement is also returned.

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;

在包含顶级 UNION 运算符的语句中不允许变量赋值。Variable assignment is not allowed in a statement containing a top-level UNION operator. 返回错误 10734。Error 10734 is returned. 若要纠正该错误,请重写查询,如下例所示。To resolve the error, rewrite the query as shown in the following example.

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

另请参阅 ALTER DATABASESee Also ALTER DATABASE