ALTER DATABASE (Transact-SQL) 相容性層級
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體
將 Transact-SQL 與查詢處理行為，設定為相容於指定的 SQL 引擎版本。 如需其他 ALTER DATABASE 選項，請參閱 ALTER DATABASE。
如需語法慣例的詳細資訊，請參閱 Transact-SQL 語法慣例。
語法
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
引數
database_name
要修改之資料庫的名稱。
COMPATIBILITY_LEVEL { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }
資料庫所要相容的 SQL Server 版本。 可以設定下列相容性層級值 (並非所有版本都支援上述所列的所有相容性層級)：
| Products | 資料庫引擎版本 | 預設相容性層級指定 | 支援的相容性層級值 |
|---|---|---|---|
| Azure SQL Database | 16 | 160 | 160、150、140、130、120、110、100 |
| Azure SQL 受控執行個體 | 16 | 150 | 160、150、140、130、120、110、100 |
| 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 |
| 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.50.x) | 10.5 | 100 | 100、90、80 |
| SQL Server 2008 (10.0.x) | 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 期間保持效能穩定性 (部分機器翻譯)。 此外，如需升級資料庫相容性層級的協助體驗，請參閱使用 Query Tuning Assistant 升級資料庫。
備註
針對 SQL Server 的所有安裝，預設相容性層級會與資料庫引擎的版本相關聯。 新資料庫會設定為這個層級，除非 model 資料庫具有更低的相容性層級。 針對從任何舊版 SQL Server 連接或還原的資料庫，資料庫會保留其現有的相容性層級 (如果其至少為 SQL Server 的執行個體所允許最低層級)。 移動相容性層級低於資料庫引擎所允許層級的資料庫時，會自動將資料庫設定為允許的最低相容性層級。 這同樣適用於系統和使用者資料庫。
SQL Server 2017 （14.x） 在附加或還原資料庫時，以及就地升級之後，預期會有下列行為：
- 如果使用者資料庫的相容性層級在升級前為 100 或更高層級，則在升級後仍會保持相同。
- 如果使用者資料庫在升級前的相容性層級為 90，則在升級後的資料庫中，相容性層級會設定為 100，這是 SQL Server 2017 (14.x) 支援的最低相容性層級。
tempdb、model、msdb與資源資料庫的相容性層級，會針對給定的資料庫引擎版本設定為預設相容性層級。master系統資料庫會繼續保有升級前的相容性層級。 這不會影響使用者資料庫行為。
針對預先存在且在較低相容性層級執行的資料庫，只要應用程式不需使用只在較高資料庫相容性層級才提供的增強功能，維持先前的資料庫相容性層級就是有效的方法。 若要進行新的開發工作，或是現有的應用程式需要使用新功能 (例如智慧型查詢處理 (部分機器翻譯) 以及某些新的 Transact-SQL)，請規劃將資料庫相容性層級升級至可用的最新層級。 如需詳細資訊，請參閱相容性層級和資料庫引擎升級。
注意
如果沒有使用者物件與相依性，通常就能安全地升級至預設的相容性層級。 如需詳細資訊，請參閱建議 - master 資料庫 (部分機器翻譯)。
使用 ALTER DATABASE 變更資料庫的相容性層級。 資料庫的新相容性層級設定會在兩個情況下生效：發出 USE <database> 命令時，或使用該資料庫作為預設資料庫內容來處理新登入時。
若要檢視資料庫目前的相容性層級，請查詢 sys.databases 目錄檢視中的 compatibility_level 資料行。
在舊版 SQL Server 中建立並升級至 SQL Server 2016 (13.x) RTM 或 Service Pack 1 的散發資料庫 (部分機器翻譯) 具有相容性層級 90，其他資料庫則不予支援。 這不會影響複寫功能。 升級至 SQL Server 較新版本的 Service Pack 和版本會增加散發資料庫的相容性層級，以符合 master 資料庫的相容性層級。
若要針對整個資料庫使用相容性層級 120 或更高版本，但同時又要加入對應至資料庫相容性層級 110 之 SQL Server 2012 (11.x) 的基數估計 (部分機器翻譯) 模型，請參閱 ALTER DATABASE SCOPED CONFIGURATION (部分機器翻譯)，特別是其關鍵字 LEGACY_CARDINALITY_ESTIMATION = ON。
Azure SQL 的備註
在 Azure SQL 資料庫 中新建立的資料庫，預設相容性層級為 SQL Server 2022 （160）。
在 Azure SQL 受控執行個體 中新建立的資料庫，預設相容性層級為 SQL Server 2019 （150）。
Microsoft不會自動更新現有資料庫的資料庫相容性層級。 這是由客戶自己決定。
Microsoft 強烈建議客戶規劃升級至最新的相容性層級，以使用最新的查詢最佳化改善項目。 如需有關如何評估 Azure SQL 資料庫 上兩個不同相容性層級之間最重要的查詢效能差異的秘訣，請參閱 Azure SQL 資料庫 中改善的查詢效能與相容性層級 130。 此文章是指相容性層級 130 和 SQL Server，但相同的方法也適用於在 SQL Server 和 Azure SQL Database 中升級至 140 或更高層級。
Azure SQL Database 上並未支援依相容性層級而異的所有功能。
尋找目前的相容性層級
若要判斷目前的相容性層級，請查詢 sys.databases 的 compatibility_level 資料行。
SELECT name, compatibility_level FROM sys.databases;
請執行下列查詢來判斷您所連線至的資料庫引擎版本。
SELECT SERVERPROPERTY('ProductVersion');
相容性層級和資料庫引擎升級
資料庫相容性層級是協助使資料庫現代化的重要工具，其能使 SQL Server 資料庫引擎得以升級，同時透過維持與升級前相同的資料庫相容性層級，來使連線的應用程式保持相同的運作狀態。 這表示可以在沒有應用程式變更 (除了資料庫連線能力之外) 的情況下，從舊版的 SQL Server (例如 SQL Server 2008 (10.0.x)) 升級至 SQL Server 或 Azure SQL Database (包括 Azure SQL 受控執行個體)。 如需詳細資訊，請參閱相容性憑證。
只要應用程式不需要使用僅限較高資料庫相容性層級的增強功能，升級 SQL Server 資料庫引擎並維持先前資料庫相容性層級就是有效的方法。 如需使用相容性層級來提供回溯相容性的詳細資訊，請參閱相容性憑證。
相容性層級和預存程序
執行預存程序時，其會使用定義所在之資料庫的目前相容性層級。 當資料庫的相容性設定改變時，也會同時自動重新編譯它的所有預存程序。
使用相容性層級來提供回溯相容性
針對舊版 SQL Server，資料庫相容性層級 (部分機器翻譯) 設定能提供所指定資料庫 (而非整個伺服器) 與 Transact-SQL 和查詢最佳化行為相關的回溯相容性。
從相容性模式 130 開始，任何會影響修正和功能的新查詢計劃只會刻意新增至新的相容性層級。 這種作法是為了將升級期間因新的查詢最佳化行為而可能引發的查詢計劃變更，所導致效能降低而產生的風險降到最低。
從應用程式的觀點來看，請使用較低相容性層級作為更安全的移轉路徑，協助您解決相關相容性層級設定所控制行為的版本差異。 目標仍然應該在某個時間點升級為最新的相容性層級，以透過受控方式繼承一些新功能，例如智慧型查詢處理。
如需詳細資訊，包括升級資料庫相容性層級的建議工作流程，請參閱升級資料庫相容性層級的最佳做法 (部分機器翻譯)。
在指定 SQL Server 版本中引進的已中止功能不會受到相容性層級保護。 這是指從 SQL Server 資料庫引擎中移除的功能。 例如，
FASTFIRSTROW提示已在 SQL Server 2012 (11.x) 中中止，並由OPTION (FAST n )提示取代。 將資料庫相容性層級設定為 110 不會還原已中止的提示。 如需已中止功能的詳細資訊，請參閱 SQL Server 中已中止的資料庫引擎功能 (部分機器翻譯)。給定 SQL Server 版本中導入的重大變更可能無法受到相容性層級的保護。 這是指 SQL Server 資料庫引擎版本之間的行為變更。 Transact-SQL 行為通常會受到相容性層級保護。 但是，已變更或已移除的系統物件不會受到相容性層級保護。
相容性層級所保護的重大變更範例是從 datetime 到 datetime2 數據類型的隱含轉換。 在資料庫相容性層級 130 之下，這些會顯示藉由考量小數部分的毫秒而改善的精確度，會導致不同的轉換值。 若要還原先前的轉換行為，請將資料庫相容性層級設定為 120 或更低。
相容性層級未保護 的重大變更範例為：
- 在系統物件中變更資料欄名稱。 在 SQL Server 2012 (11.x) 中，
sys.dm_os_sys_info中的single_pages_kb資料行已重新命名為pages_kb。 無論相容性層級為何，查詢SELECT single_pages_kb FROM sys.dm_os_sys_info都會產生錯誤 207 (無效的資料行名稱)。 - 移除的系統物件。 在 SQL Server 2012 (11.x) 中，已移除
sp_dboption。 不論相容性層級為何，語句EXEC sp_dboption 'AdventureWorks2022', 'autoshrink', 'FALSE';都會產生錯誤 2812 （Couldn't find stored procedure 'sp_dboption'）。
如需中斷性變更的詳細資訊，請參閱 SQL Server 2019 資料庫引擎功能的中斷性變更、SQL Server 2017 資料庫引擎功能的中斷性變更、SQL Server 2016 資料庫引擎功能的中斷性變更，以及 SQL Server 2014 資料庫引擎功能的中斷性變更。
- 在系統物件中變更資料欄名稱。 在 SQL Server 2012 (11.x) 中，
相容性層級之間的差異
針對 SQL Server 的所有安裝，預設相容性層級會與資料庫引擎的版本相關聯，如此表格所示。 針對新的開發工作，請務必計畫在最新的資料庫相容性層級上認證應用程式。
除非新 Transact-SQL 語法可以透過製造與使用者 Transact-SQL 程式碼的衝突來中斷現有應用程式，否則不受資料庫相容性層級管制。 這些例外狀況會記載於本文的後續章節中，其概述特定相容性層級之間的差異。
資料庫相容性層級也會提供與舊版 SQL Server 的回溯相容性，因為從任何舊版 SQL Server 附加或還原的資料庫會保留其現有相容性層級 (如果相同或高於所允許的最低相容性層級)。 這會在本文的使用相容性層級進行回溯相容性一節中討論。
從資料庫相容性層級 130 開始，任何會影響查詢計劃的新修正和功能，只會新增至可用的最新相容性層級，也稱為預設相容性層級。 這種作法是為了將升級期間因新的查詢最佳化行為而可能引發的查詢計劃變更，所導致效能降低而產生的風險降到最低。
僅新增至新版本資料庫引擎的預設相容性層級上，影響計劃的基本變更是：
針對舊版 SQL Server (追蹤旗標 4199) 所發行的查詢最佳化工具修正，會在較新版 SQL Server 的預設相容性層級中自動啟用。
適用於：SQL Server（從 SQL Server 2016 版（13.x）開始），Azure SQL 資料庫。
例如，當 SQL Server 2016 (13.x) 發行時，針對舊版 SQL Server (以及各自的相容性層級 100 到 120) 發行的所有查詢最佳化工具修正，都會變成針對使用 SQL Server 2016 (13.x) 預設相容性層級 (130) 的資料庫自動啟用。 僅 RTM 後查詢最佳化工具修正程式需要明確啟用。
若要啟用查詢最佳化工具修正程式，您可以使用下列方法：
- 在伺服器層級，使用追蹤旗標 4199。
- 在資料庫層級，使用 ALTER 資料庫範圍設定 (Transact-SQL) 中的 [
QUERY_OPTIMIZER_HOTFIXES] 選項。 - 在查詢層級，透過修改查詢來
USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'搭配查詢提示。 - 在查詢層級，使用查詢存放區提示 (預覽) (部分機器翻譯) 功能來在不進行程式碼變更的情況下使用
USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'。
之後，當 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
啟用
已啟用
啟用
已啟用
啟用已停用
啟用
已停用
啟用
已停用
啟用16 (SQL Server 2022 (16.x)) 和 12 (Azure SQL Database) 100 至 120
130 至 150
160關閉
另一
關閉
另一
關閉
另一Disabled
啟用
已啟用
啟用
已啟用
啟用已停用
啟用
已停用
啟用
已停用
啟用追蹤旗標 4199 不會保護處理錯誤結果或存取違規錯誤的「查詢最佳化工具」修正。 那些修正不會被視為選擇性。
SQL Server 和 Azure SQL Database 上發行的基數估計工具 (部分機器翻譯) 的變更，僅會在新版本的資料庫引擎預設相容性層級中啟用，但無法在先前的相容性層級上啟用。
例如，當 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))1 < 160
160已停用
啟用1 也適用於 Azure SQL Database。
特定相容性層級之間的其他差異，可在本文的後續章節中取得。
相容性層級 150 和 160 之間的差異
此節描述相容性層級 160 所引進的新行為。
| 150 或更低的相容性層級設定 | 160 的相容性層級設定 |
|---|---|
| 參數化查詢會有根據用於第一次執行之參數的單一查詢計劃。 只會快取一個查詢計劃，並用於所有參數值。 這可能會導致查詢計劃對於參數的某些值較無效率，其也稱為參數敏感性計劃。 | 參數化查詢可以有多個快取的查詢計劃，以用於參數的不同選擇性類別。 相容性層級 160 預設會啟用參數敏感性計劃最佳化。 如需詳細資訊，請參閱 PSP 最佳化 (部分機器翻譯)。 |
| 基數估計針對所有資料庫和查詢的底層資料分布和使用模式，只會使用一組預設的模型假設集合。 變更或調整任何一個那些假設的唯一方式，就是使用者使用查詢提示採取手動程序來明確指出應該使用哪一個模型假設。 產生查詢計劃之後，就無法對這個預設模型進行任何內部調整。 | 基數估計會從底層資料分布和使用模式的預設模型假設集合開始，但在執行給定查詢幾次之後，資料庫引擎便能了解哪些不同的模型假設集合可能會產生更精確的估計值，並進一步調整使用的假設，以更符合所查詢的資料集。 CE 回饋預設會在相容性層級 160 中啟用。 若需詳細資訊，請參閱 CE 回饋 (部分機器翻譯)。 |
| 資料庫引擎不會嘗試自動判斷最佳平行處理原則的程度。 如需在實例、資料庫、查詢或工作負載層級手動控制平行處理原則最大程度的資訊，請參閱 伺服器設定：平行處理原則的最大程度 | 平行處理原則的程度 (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 Server 2019 中的新功能及 SQL 資料庫中的智慧查詢處理。
相容性層級 130 和 140 之間的差異
本節描述相容性層級 140 所導入的新行為。
| 相容性層級設定為 130 或更低 | 相容性層級設定為 140 |
|---|---|
| 參考多重陳述式資料表值函式之陳述式的基數估計會使用固定的資料列猜測。 | 參考多重陳述式資料表值函式之合格陳述式的基數估計會使用函式輸出的實際基數。 這是透過針對多重陳述式資料表值函式使用交錯執行來啟用。 |
| 要求記憶體授與大小不足導致磁碟溢出的批次模式查詢，可能會持續發生連續執行的問題。 | 要求記憶體授與大小不足導致磁碟溢出的批次模式查詢，可能會改善連續執行的效能。 這是透過批次模式記憶體授與意見反應所啟用，如果批次模式運算子已發生溢出，它將會更新已快取計畫的記憶體授權大小。 |
| 要求過多記憶體授與大小的批次模式查詢，導致並行問題可能會持續發生連續執行的問題。 | 要求過多記憶體授與大小的批次模式查詢，可能會導致並行問題，可能會改善連續執行的並行存取。 這是透過批次模式記憶體授與意見反應所啟用，如果原本要求過量，它將會更新已快取計畫的記憶體授權大小。 |
| 包含聯結運算子的批次模式查詢適合用於三個實體聯結演算法，包括巢狀迴圈、雜湊聯結，以及合併聯結。 如果聯結輸入的基數估計值不正確，可能會選取不適當的聯結算法。 如果發生此問題，效能將會降低，而且將會持續使用不適當的聯結演算法，直到快取的計劃重新編譯為止。 | 有一個額外的聯結運算子，稱為自適性聯結。 如果外部組建聯結輸入的基數估計值不正確，可能會選取不適當的聯結算法。 如果發生此問題且陳述式符合自適性聯結的條件，將會動態為較小的聯結輸入使用巢狀迴圈、為較大的聯結輸入使用雜湊聯結，而不需要重新編譯。 |
| 參考資料行存放區索引的簡單式計劃並不符合批次模式執行的條件。 | 系統會捨棄參考資料行存放區索引的簡單式計畫，有利於符合批次模式執行條件的計畫。 |
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" |
批次模式與資料列模式會隨資料行存放區索引而改變：
|
批次模式與資料列模式會隨資料行存放區索引而改變：
|
| 統計資料可以自動更新。 | 自動更新統計資料的邏輯在大型資料表上會更積極。 在實務上，這應該會減少客戶已經看到在查詢上發生效能問題的案例，其中的問題在於新插入的資料列會受到頻繁查詢，但統計資料卻尚未更新以包括那些值。 |
| 在 SQL Server 2014 (12.x) 中，追蹤 2371 預設為 OFF。 | 在 SQL Server 2016 (13.x) 中，追蹤 2371 (英文) 預設為 ON。 追蹤旗標 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 之前，請先參閱 SQL Server 2014 (12.x) 資料庫引擎的新功能 (部分機器翻譯) 一文的＜查詢計劃＞一節中提供的建議。 |
| 在低於 120 的相容性層級中，將日期值轉換成字串值時，會忽略語言設定。 此行為只針對 date 類型。 請參閱範例一節中的範例 B。 | 將 date 值轉換成字串值時，系統並不會忽略語言設定。 |
EXCEPT 子句右邊的遞迴參考會建立無限迴圈。 範例區段中的範例 C 示範此行為。 |
子句中的 EXCEPT 遞歸參考會產生符合 ANSI SQL 標準的錯誤。 |
| 遞迴通用資料表運算式 (CTE) 允許複寫資料行名稱。 | 遞迴 CTE 不允許重複的資料行名稱。 |
| 如果觸發程序經過更改，則停用的觸發程序會再次啟用。 | 更改觸發程序不會變更觸發程序的狀態 (已啟用或已停用)。 |
OUTPUT INTO 資料表子句會忽略 IDENTITY_INSERT SETTING = OFF 並允許插入明確的值。 |
當 IDENTITY_INSERT 設為 OFF 時，您無法在資料表中插入識別欄位的明確值。 |
當資料庫內含項目設定為部分時，驗證 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-length 和 substring 會將每一個 Surrogate 計算為兩個字元。 | XQuery 函數 string-length 和 substring 會將每一個 Surrogate 計算為一個字元。 |
可以在遞迴通用資料表運算式 (CTE) 查詢中使用 PIVOT。 但每個分組如有多個資料列，查詢會傳回的結果將會不正確。 |
不可以在遞迴通用資料表運算式 (CTE) 查詢中使用 PIVOT。 傳回錯誤。 |
| 只有 RC4 演算法支援回溯相容性。 只有在資料庫相容性層級為 90 或 100 時，才能使用 RC4 或 RC4_128 加密新資料 (不建議。) 在 SQL Server 2012 (11.x) 中，使用 RC4 或 RC4_128 加密的材料可以在任何相容性層級中進行解密。 | 不可使用 RC4 或 RC4_128 將新材料加密。 請改用較新的演算法，例如其中一個 AES 演算法。 在 SQL Server 2012 (11.x) 中，使用 RC4 或 RC4_128 加密的材料可以在任何相容性層級中進行解密。 |
除非用於計算資料行運算式，否則 time 和 datetime2資料類型之 CAST 和 CONVERT 作業的預設樣式為 121。 若為計算資料行，預設樣式為 0。 當您建立計算資料行、將它們用於包含自動參數化的查詢或用於條件約束定義時，這種行為就會影響計算資料行。範例區段中的範例 D 顯示樣式 0 和 121 之間的差異。 其不會示範上述的行為。 如需日期和時間樣式的詳細資訊，請參閱 CAST 和 CONVERT。 |
在相容性層級 110 底下，time 和 datetime2 資料類型之 CAST 和 CONVERT 作業的預設樣式一律為 121。 如果您的查詢仰賴舊的行為，請使用低於 110 的相容性層級，或在受影響的查詢中明確指定 0 樣式。將資料庫升級到相容性層級 110 不會變更已經儲存至磁碟的使用者資料。 您必須依適當情況手動更正這項資料。 例如，如果您使用了 SELECT INTO，根據包含上述計算資料行運算式的來源建立資料表，系統就會儲存資料 (使用樣式 0) 而非計算資料行定義本身。 您必須手動將這項資料更新為符合樣式 121。 |
| 如果另一個操作數的類型為 date、time、datetime2 或 datetimeoffset 類型，則可以將 + （加法） 運算子套用至類型為 datetime 或 smalldatetime。 | 嘗試將加法運算子套用至 date、time、datetime2 或 datetimeoffset 類型的運算元，以及另一個類型為 datetime 或 smalldatetime 的運算元，將會導致錯誤 402。 |
| 分割區檢視所參考之 smalldatetime 類型的遠端資料表中的任何資料行都會對應為 datetime。 本機資料表中對應的資料行 (在選取清單的相同序數位置中) 必須為 datetime 類型。 | 分割區檢視所參考之 smalldatetime 類型的遠端資料表中的任何資料行都會對應為 smalldatetime。 本機資料表中對應的資料行 (在選取清單的相同序數位置中) 必須為 smalldatetime 類型。 在升級到 110 後，分散式分割區檢視會因為資料類型不符合而失敗。 若要解決此問題，您可以將遠端資料表的資料類型變更為 datetime 或是將本機資料庫的相容性層級設定為 100 或更低層級。 |
SOUNDEX 函數會實作以下規則：1) 分隔在 SOUNDEX 代碼中具有相同編號的兩個子音時，系統會忽略大寫 H 或大寫 W。2) 如果 character_expression 的前兩個字元在 SOUNDEX 代碼中具有相同的編號，則會將這兩個字元包括在內。 否則，如果一組並存子音有相同的 SOUNDEX 代碼數字，除了第一個子音，所有子音都會被排除在外。 |
SOUNDEX 函數會實作以下規則：1) 如果大寫 H 或大寫 W 分隔擁有相同 SOUNDEX 代碼數字的兩個子音，則會忽略右邊的子音2) 如果一組並存子音有相同的 SOUNDEX 代碼數字，除了第一個子音，所有子音都會被排除在外。其他規則可能會導致函式所 SOUNDEX 計算的值與先前相容性層級下計算的值不同。 升級至相容性層級 110 之後，您可能需要重建使用 函式的 SOUNDEX 索引、堆積或 CHECK 條件約束。 如需詳細資訊，請參閱 SOUNDEX。 |
在沒有 <order_clause> 的情況下，可以使用 STRING_AGG。 |
可以搭配選擇性的 <order_clause> 使用 STRING_AGG。 如需詳細資訊，請參閱 STRING_AGG |
相容性層級 90 和 100 之間的差異
本章節描述相容性層級 100 所導入的新行為。
| 相容性層級設定為 90 | 相容性層級設定為 100 | 影響的可能性 |
|---|---|---|
| QUOTED_IDENTIFIER設定一律會在建立多重語句數據表值函式時設定為 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 陳述式。 |
低 |
<dml_table_source>使用 INSERT 語句的 自變數會引發語法錯誤。 |
您可以在巢狀 INSERT、UPDATE、DELETE 或 MERGE 陳述式中擷取 OUTPUT 子句的結果，並將這些結果插入目標資料表或檢視表中。 這是使用 <dml_table_source> INSERT 語句的自變數來完成。 |
低 |
除非指定了 NOINDEXX，否則 DBCC CHECKDB 或 DBCC CHECKTABLE 會針對單一資料表或索引檢視表及它的所有非叢集索引和 XML 索引進行實體和邏輯一致性檢查。 不支援空間索引。 |
除非指定了 NOINDEXX，否則 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 和 xsi:type 屬性時，/e/@xsi:nil 會失敗。 但是，/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 datetime 類型的使用者定義函式會標示為不具決定性。 | 低 |
| XML 聯集和清單類型並未受到完整支援。 | 聯集和清單類型受到完整支援，包括以下功能： 清單的聯集 聯集的聯集 不可部分完成之類型的清單 聯集的清單 |
低 |
| 當此方法包含在檢視或是內嵌資料表值函式內時，將會驗證 xQuery 方法所需的 SET 選項。 | 當此方法包含在檢視表或是內嵌資料表值函式內時，將會驗證 xQuery 方法所需的 SET 選項。 如果未能正確設定此方法的 SET 選項，將會引發錯誤。 | 低 |
| 包含行結尾字元 (歸位字元和換行字元) 的 XML 屬性值不會根據 XML 標準正規化。 也就是說，會傳回這兩個字元，而不是單一換行字元。 | 包含行尾字元 (歸位字元和換行字元) 的 XML 屬性值會根據 XML 標準來正規化。 也就是說，外部剖析實體 (包括文件實體) 中的所有分行符號都會在輸入時正規化，其方式是將雙字元序列 #xD #xA 及任何未緊接著 #xA 的 #xD 轉換成單一 #xA 字元。 使用屬性來傳輸包含行結尾字元之字串值的應用程式將不會在提交這些字元時收回這些字元。 為了避免正規化的程序，請使用 XML 數值字元實體來編碼所有行尾字元。 |
低 |
資料行屬性 ROWGUIDCOL 和 IDENTITY 可能會錯誤地命名為條件約束。 例如，陳述式 CREATE TABLE T (C1 int CONSTRAINT MyConstraint IDENTITY) 會執行，但是限制式名稱不會保留，也無法供使用者存取。 |
資料行屬性 ROWGUIDCOL 和 IDENTITY 無法命名為限制式。 傳回錯誤 156。 |
低 |
使用雙向指派來更新資料行 (例如 UPDATE T1 SET @v = column_name = <expression>) 會產生非預期的結果，因為陳述式執行期間可以在其他子句 (如 WHERE 和 ON 子句) 中使用變數的即時值，而不是陳述式起始值。 這會導致述詞的意義會根據每個資料列而以非預期的方式變更。只有當相容性層級設定為 90 時，才適用這個行為。 |
使用雙向指派來更新資料行會產生預期的結果，因為陳述式執行期間只會存取資料行的陳述式起始值。 | 低 |
包含最上層 UNION 運算符的語句中允許變數指派，但會傳回非預期的結果。 在範例 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 格式)。 |
低 |
Datetime 內部函數，例如 DATEPART 不需要字串輸入值是有效的 datetime 常值。 例如，SELECT DATEPART (year, '2007/05-30') 會編譯成功。 |
日期時間內建 (如 DATEPART) 會要求字串輸入值必須是有效的日期時間常值。 當使用無效的日期時間常值時會傳回錯誤 241。 |
低 |
當參數的類型 為 char 時，會修剪 REPLACE 函式之第一個輸入參數中指定的尾端空格。 例如，在語句 SELECT '<' + REPLACE(CONVERT(char(6), 'ABC '), ' ', 'L') + '>'中，值 'ABC ' 不正確地評估為 'ABC'。 |
一律保留尾端空格。 對於依賴函式先前行為的應用程式，請在指定函式的第一個輸入參數時，使用 RTRIM 函式。 例如，下列語法會重現 SQL Server 2005 行為： SELECT '<' + REPLACE(RTRIM(CONVERT(char(6), 'ABC ')), ' ', 'L') + '>'。 |
低 |
保留關鍵字
相容性設定也會決定資料庫引擎所保留的關鍵字。 下表顯示每個相容性層級所使用的保留關鍵字。
| 相容性層級設定 | 保留關鍵字 |
|---|---|
130 |
有待決定。 |
120 |
無。 |
110 |
WITHIN GROUP、、 TRY_CONVERT、 SEMANTICKEYPHRASETABLE、 SEMANTICSIMILARITYDETAILSTABLE、 SEMANTICSIMILARITYTABLE |
100 |
CUBE、 、 MERGEROLLUP |
90 |
EXTERNAL、、 PIVOT、 UNPIVOT、 REVERT、 TABLESAMPLE |
在給定的相容性層級中，保留關鍵字包含這個層級或這個層級以下所導入的所有關鍵字。 例如，對於層級 110 的應用程式而言，上表所列出的所有關鍵字都會保留下來。 在較低的相容性層級中，層級 100 的關鍵字仍是有效的物件名稱，但對應於這些關鍵字的層級 110 語言功能則無法使用。
導入之後，關鍵字會維持保留狀態。 例如，相容性層級 90 所導入的保留關鍵字 PIVOT，也會保留在層級 100 和 110 和 120 中。
如果應用程式使用的識別碼是其相容性層級的保留關鍵字，應用程式便會失敗。 若要解決這個問題，請用方括號 ( [] ) 或引號 ( "" ) 來括住識別碼；例如，若要將使用識別碼 EXTERNAL 的應用程式升級到相容性層級 90，您可將識別碼改成 [EXTERNAL] 或 "EXTERNAL"。
如需詳細資訊，請參閱保留關鍵字。
權限
需要資料庫的 ALTER 權限。
範例
A. 變更相容性層級
下列範例會將範例資料庫資料庫的相容性層級AdventureWorks2022變更為 150，這是 SQL Server 2019 （15.x） 的預設值。
ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 150;
GO
下列範例會傳回目前資料庫的相容性層級。
SELECT name, compatibility_level
FROM sys.databases
WHERE name = db_name();
GO
B. 除非低於相容性層級 120 或更高，否則忽略 SET LANGUAGE 陳述式
下列查詢會忽略 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 時，time 和 datetime2 資料類型上的 CAST 和 CONVERT 作業的預設樣式為 121，除非上述任一類型已用於計算資料行運算式。 若為計算資料行，預設樣式為 0。
當相容性層級為 110 或更高時，time 和 datetime2 資料類型上的 CAST 和 CONVERT 作業的預設樣式一律是 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:15PM | 15:15:35.8100000 | Jun 7 2011 3:15PM | 2011-06-07 15:15:35.8130000 |
E. 變數指派 - 最上層的 UNION 運算子
在 90 的資料庫相容性層級設定下，包含最上層 UNION 運算子的陳述式中允許變數指派，但會傳回非預期的結果。 例如在下列陳述式中，會將兩個資料表之聯集中的 @v 資料行值指派給 BusinessEntityID 區域變數。 就定義來說，如果 SELECT 陳述式傳回多個值，就會將最後傳回的值指派給變數。 在此情況下，便會將最後一個值正確地指派給變數，但是也會傳回 SELECT UNION 陳述式的結果集。
ALTER DATABASE AdventureWorks2022
SET compatibility_level = 110;
GO
USE AdventureWorks2022;
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;