共用方式為

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。在 SQL Server 2014 中建立的資料庫都設定為這個層級，除非 model 資料庫具有更低的相容性層級。當資料庫從任何舊版 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新功能 (Database Engine) 主題的＜查詢計劃＞一節提供的建議。

在低於 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 的相容性層級設定

Common Language Runtime (CLR) 資料庫物件是使用 CLR 4 版執行。不過，CLR 4 版中導入的部分行為變更會加以忽略。如需詳細資訊，請參閱＜CLR 整合的新功能＞。

CLR 資料庫物件是使用 CLR 4 版執行。

XQuery 函數 string-length 和 substring 會將每一個 Surrogate 計算為兩個字元。

XQuery 函數 string-length 和 substring 會將每一個 Surrogate 計為一個字元。

可以在遞迴通用資料表運算式 (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. 如果大寫 H 或大寫 W 分隔擁有相同 SOUNDEX 代碼數字的兩個子音，則會忽略它們。

  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，則只會執行邏輯檢查。

低

搭配資料操作語言 (DML) 陳述式使用 OUTPUT 子句而且在陳述式執行期間發生執行階段錯誤時，就會終止和回復整個交易。

搭配資料操作語言 (DML) 陳述式使用 OUTPUT 子句而且在陳述式執行期間發生執行階段錯誤時，其行為取決於 SET XACT_ABORT 設定。如果 SET XACT_ABORT 為 OFF，使用 OUTPUT 子句之 DML 陳述式所產生的陳述式中止錯誤將會結束此陳述式，但是批次會繼續執行，而且不會回復交易。如果 SET XACT_ABORT 為 ON，使用 OUTPUT 子句之 DML 陳述式所產生的所有執行階段錯誤將會結束批次，而且會回復交易。

低

不會強制 CUBE 和 ROLLUP 必須為保留關鍵字。

CUBE 和 ROLLUP 在 GROUP BY 子句中為保留關鍵字。

低

Strict 驗證會套用到 XML anyType 類型的元素。

Lax 驗證會套用到 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 日期時間類型的使用者定義函數，會標示為決定性。

將 XML 常數字串值轉換成 SQL Server 日期時間類型的使用者定義函數會標示為不具決定性。

低

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 函數 {fn CONVERT()} 轉換成 ODBC 資料類型 SQL_TIMESTAMP、SQL_DATE、SQL_TIME、SQLDATE、SQL_TYPE_TIME 和 SQL_TYPE_TIMESTAMP 時，會使用樣式 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。

低

保留關鍵字

相容性設定也決定了 Database Engine 所保留的關鍵字。下表顯示每個相容性層級所使用的保留關鍵字。

相容性層級設定

保留關鍵字

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)＞。

Permissions

需要資料庫的 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)