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

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

設定 Transact-SQLTransact-SQL 及查詢處理行為,使其與指定版本的 SQL Server Database EngineSQL Server Database Engine 相容。Sets Transact-SQLTransact-SQL and query processing behaviors to be compatible with the specified version of the SQL Server Database EngineSQL 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):

產品Product 資料庫引擎版本Database Engine Version 預設相容性層級指定Default Compatibility Level Designation 支援的相容性層級值Supported Compatibility Level Values
SQL Server 2019 預覽SQL Server 2019 preview 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

備註Remarks

針對 SQL ServerSQL Server 的所有安裝,預設相容性層級設定為 Database EngineDatabase Engine的版本。For all installations of SQL ServerSQL Server, the default compatibility level is set to the version of the Database EngineDatabase Engine. 資料庫會設定為這個層級,除非 model 資料庫具有更低的相容性層級。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).
  • tempdb、model、msdb 和 Resource 資料庫的相容性層級在升級之後會設定為目前相容性層級。The compatibility levels of the tempdb, model, msdb and Resource databases are set to the current compatibility level after upgrade.
  • 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. 升級至更新版本的 Service Pack 和 SQL ServerSQL Server 版本會增加散發資料庫的相容性層級,以符合 master 資料庫的相容性層級。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最重要查詢的效能差異,請參閱在 Azure SQL Database 中使用相容性層級 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 EngineDatabase Engine 版本。To determine the version of the Database EngineDatabase 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.databasescompatibility_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 Database EngineSQL Server Database Engine,同時透過維護升級前的相同資料庫相容性層級,來讓連線的應用程式保持在運作狀態。Database compatibility level is a valuable tool to assist in database modernization, by allowing the SQL Server Database EngineSQL Server Database Engine to be upgraded, while keeping connecting applications functional status by maintaining the same pre-upgrade database compatibility level. 只要應用程式不需要使用僅限較高資料庫相容性層級的增強功能,即為升級 SQL Server Database EngineSQL 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 Database EngineSQL 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 Database EngineSQL Server Database Engine 升級至最新版本,同時維護升級前已存在的資料庫相容性層級及其可支援性狀態,建議使用 Microsoft Data Migration Assistant 工具 (DMA),執行資料庫 (可程式性物件,例如預存程序、函數、觸發程序等) 及應用程式 (使用擷取應用程式所傳送動態程式碼的工作負載追蹤) 中應用程式程式碼的靜態功能介面區驗證。To upgrade the SQL Server Database EngineSQL 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. 資料庫相容性層級提供與先前版本 SQL ServerSQL Server Transact-SQLTransact-SQL 和查詢最佳化行為相關的回溯相容性。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 Database EngineSQL Server Database Engine 更新版本上執行的應用程式雖使用較舊的資料庫相容性層級,仍可以利用伺服器層級增強功能,而完全無需變更應用程式。Because database compatibility level is a database-level setting, an application running on a newer SQL Server Database EngineSQL 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. 此外,也改善延展性,例如使用自動軟體 NUMA And 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 Database EngineSQL Server Database Engine 中已移除的功能。This refers to functionality that was removed from the SQL Server Database EngineSQL 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 Database EngineSQL Server Database Engine 版本之間的行為變更。This refers to behavior changes between versions of the SQL Server Database EngineSQL 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.

一個受相容性層級保護的重大變更範例為從日期時間轉換成日期時間 2 資料類型的隱含轉換。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_kbIn 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_dboptionIn 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. 此外,如需升級資料庫相容性層級的協助體驗,請參閱使用 Query Tuning Assistant 升級資料庫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 2019 預覽SQL Server 2019 preview 的資料庫相容性層級 150,目前在公開預覽階段。Database compatibility level 150 is currently in Public Preview for Azure SQL DatabaseAzure SQL Database and SQL Server 2019 預覽SQL Server 2019 preview. 此資料庫相容性層級將會與下一帶的查詢處理改善相關聯,超在越資料庫相容性層級 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 純量會轉換成「內嵌」在呼叫查詢中的對等關聯運算式,而這通常可讓效能大幅提升。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 純量內嵌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.
追蹤 2371 在 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中預設為「關閉」。Trace 2371 is OFF by default in SQL Server 2014 (12.x)SQL Server 2014 (12.x). 追蹤 2371SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中預設為「開啟」。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 改進 (機器翻譯)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 DatabaseSQL Database 中使用較舊的查詢最佳化工具,您必須選取相容性層級 110。To use the older query optimizer in SQL DatabaseSQL 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) Database Engine 的新功能 主題的<查詢計劃>一節提供的建議。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 的相容性層級中,將日期值轉換成字串值時,會忽略語言設定。In compatibility levels lower than 120, the language setting is ignored when converting a date value to a string value. 請注意,此行為只針對日期類型。Note that this behavior is specific only to the date type. 請參閱下方<範例>一節中的範例 B。See example B in the Examples section below. 日期值轉換成字串值時,不會忽略語言設定。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
Common Language Runtime (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 會將每一個 Surrogate 計算為兩個字元。The XQuery functions string-length and substring count each surrogate as two characters. XQuery 函數 string-lengthsubstring 會將每一個 Surrogate 計算為一個字元。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)如果大寫 H 或大寫 W 分隔擁有相同 SOUNDEX 代碼數字的兩個子音,則會忽略它們。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. Medium
當您建立或改變分割區函數時,評估此函數中的 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. Medium
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. Medium
OUTPUT 子句中允許全文檢索述詞。Full-text predicates are allowed in the OUTPUT clause. OUTPUT 子句中不允許全文檢索述詞。Full-text predicates are not allowed in the OUTPUT clause. Low
不支援 CREATE FULLTEXT STOPLISTALTER FULLTEXT STOPLISTDROP FULLTEXT STOPLISTCREATE 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 STOPLISTCREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported. Low
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. Low
使用 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. Low
除非指定了 NOINDEXX,否則 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. 除非指定了 NOINDEXX,否則 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.
Low
搭配資料操作語言 (DML) 陳述式使用 OUTPUT 子句而且在陳述式執行期間發生執行階段錯誤時,就會終止和回復整個交易。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. 搭配資料操作語言 (DML) 陳述式使用 OUTPUT 子句,而且在陳述式執行期間發生執行階段錯誤時,其行為取決於 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. Low
不會強制 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. Low
Strict 驗證會套用到 XML anyType 類型的元素。Strict validation is applied to elements of the XML anyType type. Lax 驗證會套用到 anyType 類型的元素。Lax validation is applied to elements of the anyType type. 如需詳細資訊,請參閱萬用字元元件和內容驗證For more information, see Wildcard Components and Content Validation. Low
資料操作語言陳述式無法查詢或修改特殊屬性 xsi:nilxsi:typeThe special attributes xsi:nil and xsi:type cannot be queried or modified by data manipulation language statements.

這表示當 /e/@* 忽略 xsi:nilxsi:type 屬性時,/e/@xsi:nil 會失敗。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:typeFor 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".
Low
將 XML 常數字串值轉換成 SQL ServerSQL Server 日期時間類型的使用者定義函數,會標示為決定性。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 日期時間類型的使用者定義函數會標示為不具決定性。A user-defined function that converts an XML constant string value to a SQL ServerSQL Server datetime type is marked as non-deterministic. Low
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
Low
當此方法包含在檢視表或是內嵌資料表值函式內時,不會驗證 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. Low
包含行尾字元 (歸位字元和換行字元) 的 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.
Low
資料行屬性 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. Low
使用雙向指派來更新資料行 (例如 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. Low
請參閱下方<範例>一節中的範例 E。See example E in the Examples section below. 請參閱下方<範例>一節中的範例 F。See example F in the Examples section below. Low
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 函數 {fn CONVERT()} 轉換成 ODBC 資料類型 SQL_TIMESTAMP、SQL_DATE、SQL_TIME、SQLDATE、SQL_TYPE_TIME 和 SQL_TYPE_TIMESTAMP 時,會使用樣式 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. Low
日期時間內建 (如 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. Low

保留關鍵字Reserved Keywords

相容性設定也決定了 Database EngineDatabase Engine 所保留的關鍵字。The compatibility setting also determines the keywords that are reserved by the Database EngineDatabase 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. 除非低於相容性層級 120,否則忽略 SET LANGUAGE 陳述式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 資料行值指派給 BusinessEntityID 區域變數。For 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