原帖地址：http://www.noobyard.com/article/p-weaswsen-nc.htmlhtml

ALTER DATABASE (Transact-SQL) 兼容级别

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017算法

适用对象：SQL Server（从 2008 版开始）Azure SQL 数据库Azure SQL 数据仓库并行数据仓库sql

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

有关语法约定的详细信息，请参阅 Transact-SQL 语法约定。express

语法

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

参数

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

COMPATIBILITY_LEVEL { 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
要使数据库与之兼容的 SQL Server 版本。 能够配置如下兼容级别值（并不是全部版本都支持全部以上列出的兼容级别）：缓存

执行如下查询可肯定链接到的 数据库引擎的版本。

SELECT SERVERPROPERTY('ProductVersion'); 

若要肯定当前兼容级别，请查询 sys.databases (Transact-SQL) 的 compatibility_level 列。

SELECT name, compatibility_level FROM sys.databases; 

Remarks

对于全部 SQL Server 安装，默认兼容级别都设置为 数据库引擎的版本。 除非数据库具备更低的兼容级别，不然数据库会设置为此级别。 在将数据库从 SQL Server 的任何早期版本进行升级时，若是数据库至少是该 SQL Server 实例所容许的最低级别，则它会保留现有兼容性级别。 升级兼容性级别低于容许级别的数据库会将数据库自动设置为容许的最低兼容性级别。 这既适用于系统数据库也适用于用户数据库。

附加或还原数据库时以及就地升级后，SQL Server 2017 (14.x) 应出现如下行为：

• 若是升级前用户数据库的兼容级别为 100 或更高，升级后将保持相应级别。
• 若是升级前用户数据库的兼容级别为 90，则在升级后的数据库中，兼容级别将设置为 100，该级别为 SQL Server 2017 (14.x) 支持的最低兼容级别。
• 升级后，tempdb、model、msdb 和 Resource 数据库的兼容性级别将设置为当前兼容性级别。
• master 系统数据库保留它在升级以前的兼容性级别。

使用 ALTER DATABASE 更改数据库的兼容性级别。 当发出 USE <database> 命令或使用该数据库做为默认数据库上下文来处理新登陆时，数据库的新兼容性级别设置会生效。
若要查看数据库的当前兼容级别，请查询 sys.databases 目录视图中的 compatibility_level 列。

兼容性级别和 SQL Server 升级

数据库兼容性级别是一个重要的工具，可经过容许升级 SQL Server 数据库引擎，同时经过维持相同的升级前数据库兼容性级别保持链接应用程序功能状态，从而帮助实现数据库现代化。 只要应用程序不须要利用仅在更高数据库兼容性级别中可用的加强功能，它就是升级 SQL Server 数据库引擎 和维护以前的数据库兼容性级别的有效方法。 有关利用兼容性级别获取后向兼容性的详细信息，请参阅后文的利用兼容性级别得到后向兼容性。

对于新的开发工做，或当现有应用程序须要使用新功能以及查询优化器空间中完成的性能改进时，计划将数据库兼容性级别升级到 SQL Server 中可用的最新级别，并验证应用程序可与该兼容性级别一块儿使用。 有关升级数据库兼容性级别的更多详细信息，请参阅后文的升级数据库兼容性级别的最佳作法。

若要将 SQL Server 数据库引擎 升级到最新版，同时将数据库兼容性级别维持在升级前的级别并维持其可支持性状态，建议在数据库中使用 Microsoft 数据迁移助手工具 (DMA) 对应用程序代码执行静态函数外围应用验证。 DMA 工具输出中没有关于缺失或不兼容功能的错误，可保护应用程序免受新目标版本上的任何功能回归影响。 有关 DMA 工具的详细信息，请参阅此处。

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

数据库兼容性级别设置只影响指定数据库的行为，而不影响整个服务器的行为。 数据库兼容性级别只实现与 SQL Server 的早期版本保持部分后向兼容。

从兼容性模式 130 开始，任何影响功能的新查询计划都有意仅添加到新兼容性级别中。 这样作是为了在因为查询计划更改致使性能下降而引起的升级过程当中尽可能减小风险。
从应用程序的角度来看，目标仍应在某个时间点升级到最新兼容性级别以便继承某些新功能，以及在查询优化器空间中完成的性能改进，不过是采用可控方式实现此目标。 经过将较低兼容性级别用做更安全的迁移辅助工具，可解决相关兼容性级别设置控制的行为之间存在的版本差别问题。 有关更多详细信息，包括升级数据库兼容性级别的建议工做流，请参阅后文的升级数据库兼容性级别的最佳作法。

升级数据库兼容性级别的最佳作法

有关用于升级兼容级别的建议工做流，请参阅更改数据库兼容性模式和使用查询存储。

兼容性级别和存储过程

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

兼容性级别 140 和 150 的区别

此部分介绍了随兼容性级别 150 一块儿引入的新行为。

对于 Azure SQL Database 和 SQL Server 2019 预览，数据库兼容性级别 150 目前是我的预览版。 除了数据库兼容性级别 140 中引入的改进以外，此数据库兼容性级别还将与下一代查询处理改进相关联。

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

兼容级别 130 与兼容级别 140 之间的差别

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

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

兼容级别 120 和兼容级别 130 之间的差别

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

SQL Server 2016 (13.x) 以前的早期 SQL Server 版本中处于跟踪标志 4199 下的修补程序如今默认状况下会启用。 具备兼容性模式 130。 跟踪标志 4199 仍会适用于在 SQL Server 2016 (13.x) 以后发布的新查询优化器修补程序。 若要在 SQL Database中使用较旧的查询优化器，必须选择兼容级别 110。有关跟踪标志 4199 的信息，请参阅跟踪标志 4199。

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

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

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

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

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

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

保留关键字

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

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

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

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

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

Permissions

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

示例

A. 更改兼容性级别

如下示例将 AdventureWorks2012 数据库的兼容级别更改成 110, SQL Server 2012 (11.x)。

ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 110; GO 

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

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

B. 忽略 SET LANGUAGE 语句（除非低于兼容级别 120）

只有兼容级别低于 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 

C.

对于 110 或更低的兼容级别设置，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; 

D.

此示例显示样式 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 

E.

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

F.

在包含顶级 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 (Transact-SQL) 
保留关键字 (Transact-SQL) 
CREATE DATABASE (SQL Server Transact-SQL) 
DATABASEPROPERTYEX (Transact-SQL) 
sys.databases (Transact-SQL) 
sys.database_files (Transact-SQL)
查看或更改数据库的兼容级别