ALTER DATABASE (Transact-SQL) 相容性層級

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體

將 Transact-SQL 和查詢處理行為設定為與指定版本的 SQL 引擎相容。 如需其他 ALTER DATABASE 選項,請參閱 ALTER DATABASE

如需語法慣例的詳細資訊,請參閱 Transact-SQL 語法慣例

Syntax

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

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

database_name

這是要修改之資料庫的名稱。

COMPATIBILITY_LEVEL { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }

這是要與資料庫相容之SQL Server版本。 可以設定下列相容性層級值 (並非所有版本都支援上述所列的所有相容性層級):

Products 資料庫引擎版本 預設相容性層級指定 支援的相容性層級值
SQL Server 2022 (16.x) 預覽 16 160 160,150, 140, 130, 120, 110, 100
SQL Server 2019 (15.x) 15 150 150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x) 14 140 140、130、120、110、100
Azure SQL Database 12 150 160, 150, 140, 130, 120, 110, 100
Azure SQL 受控執行個體 12 150 160, 150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x) 13 130 130、120、110、100
SQL Server 2014 (12.x) 12 120 120、110、100
SQL Server 2012 (11.x) 11 110 110、100、90
SQL Server 2008 R2 10.5 100 100、90、80
SQL Server 2008 10 100 100、90、80
SQL Server 2005 (9.x) 9 90 90、80
SQL Server 2000 (8.x) 8 80 80

重要事項

SQL Server 和 Azure SQL Database 的資料庫引擎版本號碼無法彼此相互比較,且更像是這些個別產品的內部組建編號。 Azure SQL Database 資料庫引擎是以和 SQL Server 資料庫引擎相同的程式碼基底作為基礎。 最重要的是,Azure SQL Database 資料庫引擎一律具有最新的 SQL 資料庫引擎位元。 Azure SQL Database 版本 12 比 SQL Server 版本 15 更新。

升級資料庫相容性層級的最佳做法

如需升級相容性層級的建議工作流程,請參閱在升級至較新的SQL Server期間保持效能穩定性。 此外,如需升級資料庫相容性層級的輔助體驗,請參閱 使用查詢微調小幫手升級資料庫

備註

對於所有安裝SQL Server,預設相容性層級會與 Database Engine 的版本相關聯。 新資料庫會設定為這個層級,除非 model 資料庫具有更低的相容性層級。 對於從任何舊版SQL Server附加或還原的資料庫,如果資料庫至少允許該實例SQL Server,資料庫會保留其現有的相容性層級。 將相容性層級低於 Database Engine 允許層級的資料庫,會自動將資料庫設定為允許的最低相容性層級。 這同樣適用於系統和使用者資料庫。

在附加或還原資料庫時,SQL Server 2017 (14.x) ,以及就地升級之後,預期會有下列行為:

  • 如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。
  • 如果使用者資料庫的相容性層級在升級前為 90,在升級的資料庫中,相容性層級會設定為 100,這是 SQL Server 2017 (14.x) 的最低支援相容性層級。
  • tempdb、model、msdb 和 Resource 資料庫的相容性層級會設定為指定 Database Engine 版本的預設相容性層級。
  • master 系統資料庫會繼續保有升級前的相容性層級。 這不會影響使用者資料庫行為。

對於在較低相容性層級執行的預先存在資料庫,只要應用程式不需要使用只有較高資料庫相容性層級提供的增強功能,它是維護先前資料庫相容性層級的有效方法。 對於新的開發工作,或現有應用程式需要使用 智慧型查詢處理 和一些新的 Transact-SQL 等新功能時,請規劃將資料庫相容性層級升級為最新的可用層級。 如需詳細資訊,請參閱相容性層級和資料庫引擎升級

注意

如果沒有使用者物件與相依性,通常就能安全地升級至預設的相容性層級。 如需詳細資訊,請參閱 建議 - master 資料庫

使用 ALTER DATABASE 變更資料庫的相容性層級。 資料庫的新相容性層級設定會在兩個情況下生效:發出 USE <database> 命令時,或使用該資料庫作為預設資料庫內容來處理新登入時。 若要檢視資料庫目前的相容性層級,請查詢 sys.databases 目錄檢視中的 compatibility_level 資料行。

在舊版 SQL Server 中建立的散發資料庫,並升級至 SQL Server 2016 (13.x) RTM 或 Service Pack 1 的相容性層級為 90,其他資料庫則不支援此層級。 這不會影響複寫功能。 升級至更新版本的 Service Pack 和SQL Server,會導致散發資料庫的相容性層級增加,以符合 master 資料庫的相容性層級。

若要針對整體資料庫使用資料庫相容性層級 120 或更高版本,但請加入SQL Server 2012 (11.x) 的基數估計模型,其對應至資料庫相容性層級 110,請參閱ALTER DATABASE SCOPED CONFIGURATION,特別是其關鍵字 LEGACY_CARDINALITY_ESTIMATION = ON

若要判斷目前的相容性層級,請查詢 compatibility_levelsys.databases的資料行。

SELECT name, compatibility_level FROM sys.databases;

Azure SQL資料庫的備註

2019 年 11 月起,在 Azure SQL Database 中,新建立資料庫的預設相容性層級為 150。 Microsoft 不會更新現有資料庫的資料庫相容性層級。 這是由客戶自己決定。 Microsoft 強烈建議客戶規劃升級至最新的相容性層級,以利用最新的查詢最佳化改善項目。

如需如何在 Azure SQL 資料庫上評估兩個不同相容性層級之間最重要的查詢效能差異的詳細資訊,請參閱Azure SQL 資料庫中改善相容性層級 130 的查詢效能。 本文指的是相容性層級 130 和SQL Server,但相同的方法適用于在 SQL Server 和 Azure SQL Database 中升級到 140 或更高層級的方法。

若要判斷您所連接的 Database Engine 版本,請執行下列查詢。

SELECT SERVERPROPERTY('ProductVersion');

Azure SQL 資料庫不支援因相容性層級而異的所有功能。

相容性層級和資料庫引擎升級

資料庫相容性層級是協助資料庫現代化的重要工具,可讓SQL Server Database Engine 升級,同時維持相同的功能狀態,藉由維護相同的升級前資料庫相容性層級來連接應用程式。 這表示您可以從舊版的 SQL Server (升級 SQL Server至 SQL Server 2008) 或 Azure SQL Database (,包括不含應用程式變更Azure SQL 受控執行個體) (,但資料庫連線) 除外。 如需詳細資訊,請參閱相容性憑證

只要應用程式不需要使用只有較高資料庫相容性層級提供的增強功能,就可以有效升級 SQL Server Database Engine,並維護先前的資料庫相容性層級。 如需使用相容性層級來提供回溯相容性的詳細資訊,請參閱相容性憑證

相容性層級和預存程式

當預存程式執行時,它會使用其定義所在資料庫的目前相容性層級。 當資料庫的相容性設定改變時,也會同時自動重新編譯它的所有預存程序。

使用相容性層級來提供回溯相容性

資料庫相容性層級設定提供與舊版SQL Server的回溯相容性,與 Transact-SQL 和查詢優化行為有關,僅適用于指定的資料庫,而非整個伺服器。

從相容性模式 130 開始,任何會影響修正和功能的新查詢計劃只會刻意新增至新的相容性層級。 這種作法是為了將升級期間因新的查詢最佳化行為而可能引發的查詢計劃變更,所導致效能降低而產生的風險降到最低。

從應用程式的觀點來看,請使用較低相容性層級作為更安全的移轉路徑,協助您解決相關相容性層級設定所控制行為的版本差異。 目標仍然應該在某個時間點升級為最新的相容性層級,以透過受控方式繼承一些新功能,例如智慧型查詢處理

如需詳細資訊,包括升級資料庫相容性層級的建議工作流程,請參閱 升級資料庫相容性層級的最佳做法

  • 特定SQL Server版本中引進的已停止功能不會受到相容性層級的保護。 這是指已從 SQL Server Database Engine 移除的功能。 例如,SQL Server FASTFIRSTROW 2012 (11.x) 中已停止提示,並將 取代為 OPTION (FAST n ) 提示。 將資料庫相容性層級設定為 110 不會還原已停用的提示。 如需已停止功能的詳細資訊,請參閱SQL Server 中已停止的資料庫引擎功能

  • 給定SQL Server版本中導入的重大變更可能不受相容性層級的保護。 這是指 SQL Server Database Engine 版本之間的行為變更。 Transact-SQL 行為通常會受到相容性層級的保護。 但是,已變更或已移除的系統物件不會受到相容性層級保護。

    一個受相容性層級保護的重大變更範例為從日期時間轉換成日期時間 2 資料類型的隱含轉換。 在資料庫相容性層級 130 之下,這些會顯示藉由考量小數部分的毫秒而改善的精確度,會導致不同的轉換值。 若要還原先前的轉換行為,請將資料庫相容性層級設定為 120 或更低。

    相容性層級未保護 的重大變更範例為:

    • 在系統物件中變更資料欄名稱。 在 SQL Server 2012 (11.x) 中的資料行 single_pages_kbsys.dm_os_sys_info 已重新命名為 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安裝,預設相容性層級會與 Database Engine 的版本相關聯,如下所示。 針對新的開發工作,請一律規劃在最新的資料庫相容性層級認證應用程式。

新的 Transact-SQL 語法不會受到資料庫相容性層級的管制,除非它們可以藉由建立與使用者 Transact-SQL 程式碼的衝突來中斷現有的應用程式。 這些例外狀況會記載於本文的後續章節中,其概述特定相容性層級之間的差異。

資料庫相容性層級也提供與舊版SQL Server的回溯相容性,因為從任何舊版SQL Server附加或還原的資料庫會保留其現有相容性層級 (,如果相同或高於允許的最低相容性層級) 。 這會在本文的使用相容性層級進行回溯相容性一節中討論。

從資料庫相容性層級 130 開始,任何影響查詢計劃的新修正和功能都只新增至可用的最新相容性層級,也稱為預設相容性層級。 這種作法是為了將升級期間因新的查詢最佳化行為而可能引發的查詢計劃變更,所導致效能降低而產生的風險降到最低。

僅新增至新版 Database Engine 預設相容性層級的基本計畫影響變更如下:

  1. 針對追蹤旗標 4199 下先前SQL Server版本發行的查詢最佳化工具修正,會在較新SQL Server版本的預設相容性層級中自動啟用適用于:SQL Server (從 SQL Server 2016 (13.x) ) 和 Azure SQL Database 開始。

    例如,發行 SQL Server 2016 (13.x) 時, 針對先前SQL Server版本 (和個別相容性層級 100 到 120) 發行的所有查詢最佳化工具修正程式,都會針對使用 SQL Server 2016 (13.x) 預設相容性層級 (130) 的資料庫自動啟用。 僅 RTM 後查詢最佳化工具修正程式需要明確啟用。

    若要啟用查詢最佳化工具修正程式,您可以使用下列方法:

    之後,SQL Server 2017 (14.x) 發行時,SQL Server 2016 (13.x) RTM 之後發行的所有查詢最佳化工具修正程式都會使用 SQL Server 2017 (14.x) 預設相容性層級 (140) 自動啟用資料庫。 這是包含所有舊版修正的累計行為。 同樣地,僅 RTM 後查詢最佳化工具修正程式需要明確啟用。

    下表摘要說明這個行為:

    資料庫引擎 (DE) 版本 資料庫相容性層級 TF 4199 來自所有先前資料庫相容性層級的的 QO 變更 RTM 後 DE 版本的 QO 變更
    13 (SQL Server 2016 (13.x) ) 100 至 120


    130
    關閉
    另一

    關閉
    另一
    Disabled
    啟用

    已啟用
    啟用
    已停用
    啟用

    已停用
    啟用
    14 (SQL Server 2017 (14.x) ) 100 至 120


    130


    140
    關閉
    另一

    關閉
    另一

    關閉
    另一
    Disabled
    啟用

    已啟用
    啟用

    已啟用
    啟用
    已停用
    啟用

    已停用
    啟用

    已停用
    啟用
    15 (SQL Server 2019 (15.x) ) 和 12 (Azure SQL Database) 100 至 120


    130 至 140


    150
    關閉
    另一

    關閉
    另一

    關閉
    另一
    Disabled
    啟用

    已啟用
    啟用

    已啟用
    啟用
    已停用
    啟用

    已停用
    啟用

    已停用
    啟用

    追蹤旗標 4199 不會保護處理錯誤結果或存取違規錯誤的「查詢最佳化工具」修正。 那些修正不會被視為選擇性。

  2. SQL Server和 Azure SQL Database 上發行的基數估算器變更只會在新 Database Engine 版本的預設相容性層級中啟用,但無法在先前的相容性層級上啟用。

    例如,SQL Server 2016 (13.x) 發行時,基數估計程式的變更僅適用于使用 SQL Server 2016 (13.x) 預設相容性層級 (130) 的資料庫。 先前的相容性層級會保留SQL Server 2016 (13.x) 之前可用的基數估計行為。

    稍後,SQL Server 2017 (14.x) 發行時,較新的基數估計程式變更僅適用于使用 SQL Server 2017 (14.x) 預設相容性層級 (140) 的資料庫。 資料庫相容性層級 130 會保留 SQL Server 2016 (13.x) 基數估計行為。

    下表摘要說明這個行為:

    資料庫引擎版本 資料庫相容性層級 新版本 CE 變更
    13 (SQL Server 2016 (13.x) ) < 130
    130
    已停用
    啟用
    14 (SQL Server 2017 (14.x) ) 1 < 140
    140
    已停用
    啟用
    15 (SQL Server 2019 (15.x) ) 1 < 150
    150
    已停用
    啟用
    16 (SQL Server 2022 (16.x) Preview) 1 < 160
    160
    已停用
    啟用

    1也適用于Azure SQL資料庫。

特定相容性層級之間的其他差異,可在本文的後續章節中取得。

相容性層級 150 與層級 160 之間的差異

本節說明與相容性層級 160 一起引進的新行為。

相容性層級設定為 150 或更低 相容性層級設定 160
參數化查詢會根據第一次執行所使用的參數,建立單一查詢計劃。 只會快取一個查詢計劃,並用於所有參數值。 這可能會導致查詢計劃對參數的某些值沒有效率,也稱為參數敏感性計畫。 參數化查詢可以有多個快取查詢計劃,以用於參數的不同選擇性類別。 預設會在相容性層級 160 中啟用參數敏感性計畫優化。 如需詳細資訊,請參閱 PSP 優化
基數估計只會針對所有資料庫和查詢使用基礎資料散發和使用模式,使用一組預設的模型假設。 變更或調整其中任一假設的唯一方式,是當使用者執行手動程式來明確指出應該使用哪一個模型假設,方法是透過使用查詢提示。 產生查詢計劃之後,無法對這個預設模型進行任何內部調整。 基數估計會從基礎資料散發和使用模式的預設模型假設集開始,但在給定查詢的特定執行次數之後,Database Engine 會瞭解哪些不同的模型假設可能會產生更精確的估計值,因此會調整使用的假設,以更符合所查詢的資料集。 CE 意見反應預設會在相容性層級 160 中啟用。 如需詳細資訊,請參閱 CE 意見反應
資料庫引擎不會嘗試自動判斷最佳平行處理原則的程度。 如需在實例、資料庫、查詢或工作負載層級上手動控制平行處理原則的最大程度 (MAXDOP) 的資訊,請參閱 設定平行處理原則的最大程度伺服器組態選項 平行處理原則的程度 (DOP) 意見反應會根據經過的時間和等候,識別重複查詢的平行處理原則效率不佳,藉此改善查詢效能。 如果平行處理原則使用方式視為效率不佳,DOP 意見反應會降低下一次執行查詢的 DOP、從任何已設定的 DOP,並確認其是否有説明。 DOP 意見反應預設不會啟用。 若要啟用 DOP 意見反應,請在 DOP_FEEDBACK 資料庫中啟用資料庫範圍設定。 如需詳細資訊,請參閱 DOP 意見反應

相容性層級 140 與層級 150 之間的差異

本節描述相容性層級 150 所導入的新行為。

相容性層級設定為 140 或更低 相容性層級設定為 150
關聯式資料倉儲和分析工作負載可能無法利用資料行存放區索引,因為 OLTP 額外負荷、缺少廠商支援或其他限制。 若沒有資料行存放區索引,這些工作負載就無法從批次執行模式中獲益。 分析工作負載現在可使用批次執行模式,而不需要資料行存放區索引。 如需詳細資訊,請參閱資料列存放區上的批次模式
要求不足的記憶體授權大小導致溢出到磁碟的資料列模式查詢,可能會在連續執行時繼續發生問題。 要求不足的記憶體授權大小導致溢出到磁碟的資料列模式查詢,可能已改進在連續執行時的效能。 如需詳細資訊,請參閱 資料列模式記憶體授與意見反應
要求過多的記憶體授權大小導致發生並行問題的資料列模式查詢,可能會在連續執行時繼續發生問題。 要求過多的記憶體授權大小導致發生並行問題的資料列模式查詢,可能已改進在連續執行時的並行。 如需詳細資訊,請參閱 資料列模式記憶體授與意見反應
參考 T-SQL 純量 UDF 的查詢會使用反覆引動、缺少成本,以及強制序列執行。 T-SQL 純量 UDF 會轉換成「內嵌」至呼叫查詢的對等關聯運算式,通常會導致大幅提升效能。 如需詳細資訊,請參閱 T-SQL 純量內嵌
資料表變數針對基數估計值使用固定猜測。 如果實際的資料列數目遠高於猜測的值,下游作業的效能可能會受到負面影響。 新方案會使用在第一次編譯時遇到的資料表值函式實際基數,而不是定點猜測。 如需詳細資訊,請參閱資料表變數延遲編譯

如需在資料庫相容性層級 150 中啟用之查詢處理功能的詳細資訊,請參閱SQL 資料庫中 SQL Server 2019 的新功能智慧型查詢處理

相容性層級 130 與層級 140 之間的差異

本節描述相容性層級 140 所導入的新行為。

相容性層級設定為 130 或更低 相容性層級設定為 140
參考多重陳述式資料表值函式之陳述式的基數估計會使用固定的資料列猜測。 參考多重陳述式資料表值函式之合格陳述式的基數估計會使用函式輸出的實際基數。 這是透過針對多重陳述式資料表值函式使用交錯執行來啟用。
要求不足的記憶體授權大小導致溢出到磁碟的批次模式查詢,可能會在連續執行時繼續發生問題。 要求不足的記憶體授權大小導致溢出到磁碟的批次模式查詢,可能已改進在連續執行時的效能。 這是透過批次模式記憶體授與意見反應所啟用,如果批次模式運算子已發生溢出,它將會更新已快取計畫的記憶體授權大小。
要求過多的記憶體授權大小導致發生並行問題的批次模式查詢,可能會在連續執行時繼續發生問題。 要求過多的記憶體授權大小導致發生並行問題的批次模式查詢,可能已改進在連續執行時的並行。 這是透過批次模式記憶體授與意見反應所啟用,如果原本要求過量,它將會更新已快取計畫的記憶體授權大小。
包含聯結運算子的批次模式查詢適合用於三個實體聯結演算法,包括巢狀迴圈、雜湊聯結,以及合併聯結。 如果聯結輸入的基數估計不正確,可能會選取不適當的聯結演算法。 如果發生此問題,效能將會降低,且不適當的聯結演算法將會保持在使用中,直到快取的計畫重新編譯為止。 有一個額外的聯結運算子,稱為自適性聯結。 如果外部組件聯結輸入的基數估計不正確,可能會選取不適當的聯結演算法。 如果發生此問題且陳述式符合自適性聯結的條件,將會動態為較小的聯結輸入使用巢狀迴圈,為較大的聯結輸入使用雜湊聯結,不需要重新編譯。
參考資料行存放區索引的 Trivial 計畫不符合批次模式執行資格。 系統會捨棄參考資料行存放區索引的簡單式計畫,有利於符合批次模式執行條件的計畫。
sp_execute_external_script UDX 運算子只能在資料列模式中執行。 sp_execute_external_script UDX 運算子符合批次模式執行的條件。
TVF) 沒有交錯執行 (多語句資料表值函式 交錯執行多重陳述式 TVF 以提升計畫品質。

SQL Server 2017 之前的 SQL Server 較早版本中,追蹤旗標 4199 之下的修正程式現在已經預設啟用。 具備相容性模式 140。 追蹤旗標 4199 將仍然適用於在 SQL Server 2017 之後發行的新查詢最佳化工具修正程式。 如需有關追蹤旗標 4199 的詳細資訊,請參閱追蹤旗標 4199

相容性層級 120 與層級 130 之間的差異

本節描述相容性層級 130 所導入的新行為。

相容性層級設定為 120 或更低 相容性層級設定為 130
INSERT-SELECT 陳述式中的 INSERT 是單一執行緒。 INSERT-SELECT 陳述式中的 INSERT 是多執行緒,或可以有平行計畫。
針對經記憶體最佳化的資料表進行的查詢會執行單一執行緒。 針對經記憶體最佳化的資料表進行的查詢,現在可以有平行計畫。
已導入 SQL 2014 基數估計工具 CardinalityEstimationModelVersion="120" 搭配基數估計模型 130 取得進一步的基數估計 ( CE) 改進,這可從查詢計畫中看到。 CardinalityEstimationModelVersion="130"
批次模式與資料列模式會隨資料行存放區索引而改變:
  • 在具有資料行存放區索引的資料表上執行的排序會以資料列模式執行
  • 視窗型函式彙總會以資料列模式 (例如 LAGLEAD) 運作
  • 使用多個不同子句在資料行存放區資料表上進行的查詢會以資料列模式運作
  • 在 MAXDOP 1 之下執行,或以資料列模式執行的序列計畫
批次模式與資料列模式會隨資料行存放區索引而改變:
  • 在具有資料行存放區索引的表格上進行的排序現在會以批次模式運作
  • 視窗型彙總現在會以批次模式 (例如LAGLEAD) 運作
  • 使用多個不同子句在資料行存放區資料表上進行的查詢會以批次模式運作
  • 在 MAXDOP 1 下執行的查詢,或以批次模式執行序列計畫
統計資料可以自動更新。 自動更新統計資料的邏輯在大型資料表上會更積極。 在實務上,這應該可減少客戶在查詢時發現經常查詢新插入資料列的效能問題,但統計資料尚未更新以包含這些值的情況。
追蹤 2371 預設為 OFF SQL Server 2014 (12.x) 。 追蹤 2371預設為 ON SQL Server 2016 (13.x) 。 追蹤旗標 2371 會告知自動統計資料更新程式,在擁有很多資料列的資料表中,以較小但更聰明的資料列子集方式進行取樣。

其中一項改進是在樣本中包含更多最近插入的資料列。

另一項改進是讓查詢在更新統計資料程序執行時執行,而不是封鎖查詢。
對於層級 120,統計資料會由單一執行緒程序進行取樣。 對於層級 130,統計資料則會由多執行緒程序 (平行處理序) 進行取樣。
其限制為 253 個傳入外部索引鍵。 指定資料表最多可由 10,000 個傳入外部索引鍵或類似參考進行參考。 相關限制,請參閱 Create Foreign Key Relationships
允許使用已被取代的 MD2、MD4、MD5、SHA 和 SHA1 雜湊演算法。 只允許使用 SHA2_256 和 SHA2_512 雜湊演算法。
SQL Server 2016 (13.x) 包含某些資料類型轉換的改善,有些 (大部分不常見) 作業。 如需詳細資料,請參閱處理某些資料類型和不常見作業的 SQL Server 2016 改進 (機器翻譯)
STRING_SPLIT 式無法使用。 STRING_SPLIT 函式適用於相容性層級 130 或以上。 如果您的資料庫相容性層級低於 130,SQL Server將無法找到並執行 STRING_SPLIT 函式。

根據預設,SQL Server 2016 (13.x) 之前的舊版SQL Server追蹤旗標 4199 底下的修正。 具備相容性模式 130。 追蹤旗標 4199 仍然適用于SQL Server 2016 (13.x) 之後發行的新查詢最佳化工具修正。 若要在 SQL Database中使用較舊的查詢最佳化工具,您必須選取相容性層級 110。 如需有關追蹤旗標 4199 的詳細資訊,請參閱追蹤旗標 4199

較低相容性層級與層級 120 之間的差異

本章節描述相容性層級 120 所導入的新行為。

相容性層級設定為 110 或更低 相容性層級設定為 120
使用舊版的查詢最佳化工具。 SQL Server 2014 (12.x) 包含建立和優化查詢計劃的元件大幅改善。 這個新的查詢最佳化工具功能取決於資料庫相容性層級 120 的使用。 新的資料庫應用程式應該使用資料庫相容性層級 120 加以開發,以便充分利用這些改良功能。 從舊版SQL Server移轉的應用程式應該經過仔細測試,以確認已維護或改善良好的效能。 如果效能降低,您可以將資料庫相容性層級設定為 110 或更低的數字,以便使用舊的查詢最佳化工具方法。

資料庫相容性層級 120 會使用新的基數估計工具,其經過調整適合於新型資料倉儲和 OLTP 工作負載。 由於效能問題,將資料庫相容性層級設定為 110 之前,請參閱Database Engine SQL Server 2014 (12.x) 新功能一文中的查詢計劃一節中的建議。
在低於 120 的相容性層級中,將日期值轉換成字串值時,會忽略語言設定。 請注意,此行為只針對日期類型。 請參閱下方<範例>一節中的範例 B。 日期 值轉換成字串值時,不會忽略語言設定。
EXCEPT 子句右邊的遞迴參考會建立無限迴圈。 下方<範例>一節中的範例 C 會示範此行為。 EXCEPT 子句中的遞迴參考會產生符合 ANSI SQL 標準的錯誤。
遞迴通用資料表運算式 (CTE) 允許複寫資料行名稱。 遞迴 CTE 不允許重複的資料行名稱。
如果觸發程序經過更改,則停用的觸發程序會再次啟用。 改變觸發程式不會變更觸發程式 (啟用或停用狀態) 。
OUTPUT INTO 資料表子句會忽略 IDENTITY_INSERT SETTING = OFF 並允許插入明確的值。 當 設定為 OFF 時 IDENTITY_INSERT ,您無法在資料表中插入識別欄位的明確值。
當資料庫內含項目設定為部分時,驗證 MERGE 陳述式的 OUTPUT 子句中的 $action 欄位可能會傳回定序錯誤。 語句子句 MERGE$action 傳回值的定序是資料庫定序,而不是伺服器定序,而且不會傳回定序衝突錯誤。
SELECT INTO 陳述式永遠都會建立單一執行緒的插入作業。 SELECT INTO 陳述式可建立平行插入作業。 當插入大量資料列時,平行作業可以提升效能。

較低相容性層級和層級 100 和 110 之間的差異

本章節描述相容性層級 110 所導入的新行為。 本節也適用於 110 以上的相容性層級。

相容性層級設定為 100 或更低 至少為 110 的相容性層級設定
Common Language Runtime (CLR) 資料庫物件是使用 CLR 4 版執行。 不過,CLR 4 版中導入的部分行為變更會加以忽略。 如需相關資訊,請參閱 CLR 整合的新功能 CLR 資料庫物件是使用 CLR 4 版執行。
XQuery 函數 string-lengthsubstring 會將每一個 Surrogate 計算為兩個字元。 XQuery 函數 string-lengthsubstring 會將每一個 Surrogate 計算為一個字元。
可以在遞迴通用資料表運算式 (CTE) 查詢中使用 PIVOT。 但每個分組如有多個資料列,查詢會傳回的結果將會不正確。 PIVOT 在 CTE) 查詢 (遞迴通用資料表運算式中不允許。 傳回錯誤。
只有 RC4 演算法支援回溯相容性。 只有在資料庫相容性層級為 90 或 100 時,才能使用 RC4 或 RC4_128 加密新資料 (不建議使用。) 在 2012 SQL Server 2012 (11.x) 中,任何相容性層級都可以解密使用 RC4 或RC4_128加密的資料。 無法使用 RC4 或RC4_128來加密新材質。 請改用較新的演算法,例如其中一個 AES 演算法。 在 SQL Server 2012 (11.x) 中,任何相容性層級都可以解密使用 RC4 或RC4_128加密的資料。
除非用於計算資料行運算式,否則 timedatetime2資料類型之 CASTCONVERT 作業的預設樣式為 121。 若為計算資料行,預設樣式為 0。 當您建立計算資料行、將它們用於包含自動參數化的查詢或用於條件約束定義時,這種行為就會影響計算資料行。

下方<範例>一節中的範例 D 會顯示樣式 0 與 121 之間的差異。 此範例不會示範上述的行為。 如需日期和時間樣式的詳細資訊,請參閱 CAST 和 CONVERT
在相容性層級 110 底下,timedatetime2 資料類型之 CASTCONVERT 作業的預設樣式一律為 121。 如果您的查詢仰賴舊的行為,請使用低於 110 的相容性層級,或在受影響的查詢中明確指定 0 樣式。

將資料庫升級至相容性層級 110 並不會變更已儲存至磁片的使用者資料。 您必須依適當情況手動更正這項資料。 例如,如果您使用了 SELECT INTO,根據包含上述計算資料行運算式的來源建立資料表,系統就會儲存資料 (使用樣式 0) 而非計算資料行定義本身。 您必須手動將這項資料更新為符合樣式 121。
如果其他運算元的類型為 datetime 或Smalldatetime則 + (加法) 運算子可以套用至datetimedatetime2datetimeoffset類型的運算元。 嘗試將加號套用至 datetimedatetime2datetimeoffset 類型的運算元,以及 datetimeSmalldatetime 類型的運算元會導致錯誤 402。
分割區檢視所參考之 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
STRING_AGG 在沒有 的情況下,可以使用 <order_clause> STRING_AGG 可使用選擇性 <order_clause> 的 。 如需詳細資訊,請參閱 STRING_AGG

相容性層級 90 與層級 100 之間的差異

本章節描述相容性層級 100 所導入的新行為。

相容性層級設定為 90 相容性層級設定為 100 影響的可能性
如果不論工作階段層級設定為何都會建立多重陳述式資料表值函式,則 QUOTED_IDENTIFER 設定一定會針對這種函數設定為 ON。 當建立多重陳述式資料表值函式時,可接受 QUOTED IDENTIFIER 工作階段設定。
當您建立或改變分割區函數時,評估此函數中的 datetimesmalldatetime 常值時會假設 US_English 為語言設定。 目前的語言設定可用來評估分割區函數中的 datetimesmalldatetime 常值。
INSERTSELECT INTO 陳述式中允許 (但會忽略) FOR BROWSE 子句。 SELECT INTO 語句中 INSERT 不允許子 FOR BROWSE 句。
OUTPUT 子句中允許全文檢索述詞。 子句中 OUTPUT 不允許全文檢索述詞。
不支援 CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTDROP FULLTEXT STOPLIST。 系統停用字詞表會自動與新的全文檢索索引產生關聯。 支援 CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTDROP FULLTEXT STOPLIST
MERGE 不會強制執行為保留關鍵字。 MERGE 是完整的保留關鍵字。 100 和 90 相容性層級之下都支援 MERGE 陳述式。
<使用 INSERT 語句的 dml_table_source > 引數會引發語法錯誤。 您可以在巢狀 INSERT、UPDATE、DELETE 或 MERGE 陳述式中擷取 OUTPUT 子句的結果,並將這些結果插入目標資料表或檢視表中。 這會使用 < INSERT 語句的 dml_table_source > 引數來完成。
除非指定了 NOINDEXX,否則 DBCC CHECKDBDBCC CHECKTABLE 會針對單一資料表或索引檢視表及它的所有非叢集索引和 XML 索引進行實體和邏輯一致性檢查。 不支援空間索引。 除非指定了 NOINDEXX,否則 DBCC CHECKDBDBCC 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 必須為保留關鍵字。 CUBEROLLUP 在 GROUP BY 子句中為保留關鍵字。
Strict 驗證會套用到 XML anyType 類型的元素。 Lax 驗證會套用到 anyType 類型的元素。 如需詳細資訊,請參閱萬用字元元件和內容驗證
資料操作語言陳述式無法查詢或修改特殊屬性 xsi:nilxsi:type

這表示當 /e/@* 忽略 xsi:nilxsi:type 屬性時,/e/@xsi:nil 會失敗。 但是,/e 會傳回 xsi:nilxsi:type 屬性以便與 SELECT xmlCol 一致,即使 xsi:nil = "false" 也是如此。
特殊屬性 xsi:nilxsi:type 會儲存為一般屬性,而且可進行查詢和修改。

例如,執行 SELECT x.query('a/b/@*') 查詢會傳回所有屬性,包括 xsi:nilxsi:type。 若要在查詢中排除這些類型,請將 @* 取代為 @*[namespace-uri(.) != "insert xsi namespace uri",而非 (local-name(.) = "type"local-name(.) ="nil".
將 XML 常數位符串值轉換成SQL Server日期時間類型的使用者定義函數會標示為具決定性。 將 XML 常數位符串值轉換成SQL Server datetime 類型的使用者定義函數會標示為不具決定性。
不支援 XML 等位和清單類型。 聯集和清單類型受到完整支援,包括以下功能:

清單的聯集

聯集的聯集

不可部分完成之類型的清單

聯集的清單
當方法包含在檢視或內嵌資料表值函式中時,不會驗證 xQuery 方法所需的 SET 選項。 當此方法包含在檢視表或是內嵌資料表值函式內時,將會驗證 xQuery 方法所需的 SET 選項。 如果未能正確設定此方法的 SET 選項,將會引發錯誤。
包含行尾字元的 XML 屬性值 (歸位字元和換行字元) 不會根據 XML 標準正規化。 也就是說,會傳回這兩個字元,而不是單一換行字元。 包含行尾字元 (歸位字元和換行字元) 的 XML 屬性值會根據 XML 標準來正規化。 也就是說,外部剖析之實體 (包括文件實體) 內的所有分行符號都會在輸入上正規化,其方式是將雙字元序列 #xD #xA 及任何緊接著 #xA 的 #xD 轉換成單一 #xA 字元。

使用屬性來傳輸包含行尾字元之字串值的應用程式將不會在提交這些字元時收回這些字元。 為了避免正規化的程序,請使用 XML 數值字元實體來編碼所有行尾字元。
資料行屬性 ROWGUIDCOLIDENTITY 可能會錯誤地命名為條件約束。 例如,語句 CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) 會執行,但不會保留條件約束名稱,而且使用者無法存取。 資料行屬性 ROWGUIDCOLIDENTITY 無法命名為條件約束。 傳回錯誤 156。
使用雙向指派來更新資料行 (例如 UPDATE T1 SET @v = column_name = <expression>) 會產生非預期的結果,因為陳述式執行期間可以在其他子句 (如 WHEREON 子句) 中使用變數的即時值,而不是陳述式起始值。 這會導致述詞的意義會根據每個資料列而以非預期的方式變更。

只有當相容性層級設定為 90 時,才適用這個行為。
使用雙向指派來更新資料行會產生預期的結果,因為陳述式執行期間只會存取資料行的陳述式起始值。
在包含最上層 UNION 運算子的陳述式中允許使用變數指派,但是會傳回非預期的結果。 深入瞭解 範例 E. 在包含最上層 UNION 運算子的陳述式中,不允許使用變數指派。 傳回錯誤 10734。 在 範例 E中尋找建議的重寫。
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 格式)。
日期時間內建 (如 DATEPART) 不會要求字串輸入值必須是有效的日期時間常值。 例如,SELECT DATEPART (year, '2007/05-30') 會編譯成功。 日期時間內建 (如 DATEPART) 會要求字串輸入值必須是有效的日期時間常值。 當使用無效的日期時間常值時會傳回錯誤 241。
當參數的類型為 Char 時,在 REPLACE 函式的第一個輸入參數內所指定尾端空格將會被修剪。 例如,在 SELECT '' + REPLACE 語句中,SELECT ' < ' + REPLACE (CONVERT (char (6) ,'ABC ') ,', 'L') + ' > '','ABC' 的值不正確地評估為 'ABC'。 一律保留尾端空格。 如果是依賴舊版函式行為的應用程式,則當您為此函式指定第一個輸入參數時,請使用 RTRIM 函式。 例如,下列語法會重現 SQL Server 2005 行為 SELECT ' < ' + REPLACE (RTRIM (CONVERT (char (6) , 'ABC ') ) , ' ', 'L') + ' > '' 。

保留關鍵字

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

相容性層級設定 保留關鍵字
130 有待決定。
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"

如需詳細資訊,請參閱保留關鍵字

權限

需要資料庫的 ALTER 權限。

範例

A. 變更相容性層級

下列範例會將AdventureWorks2019範例資料庫資料庫的相容性層級變更為 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 時,timedatetime2資料類型的預設樣式 CASTCONVERT 為 121,但計算資料行運算式中使用任一類型時除外。 若為計算資料行,預設樣式為 0。

當相容性層級為 110 或更新版本時,timedatetime2資料類型的預設樣式 CASTCONVERT 一律為 121。 如需詳細資訊 ,請參閱較低相容性層級與層級 100 和 110 之間的差異

如需日期和時間樣式的詳細資訊,請參閱 CAST 和 CONVERT

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

這會傳回如下的結果:

TimeStyle0 TimeStyle121 Datetime2Style0 Datetime2Style121
下午 3:15 15:15:35.8100000 2011 年 6 月 7 日下午 3:15 2011-06-07 15:15:35.8130000

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;

下一步

如需資料庫相容性層級和相關概念的詳細資訊,請參閱下列文章: