檢視或變更資料庫的相容性層級View or Change the Compatibility Level of a Database

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

此主題描述如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中檢視或變更資料庫的相容性層級。This topic describes how to view or change the compatibility level of a database in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 在變更資料庫的相容性層級之前,您應該先了解此變更對應用程式的影響。Before you change the compatibility level of a database, you should understand the impact of the change on your applications. 如需詳細資訊,請參閱 ALTER DATABASE 相容性層級 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

本主題內容In This Topic

開始之前Before You Begin

安全性Security

權限Permissions

需要資料庫的 ALTER 權限。Requires ALTER permission on the database.

使用 SQL Server Management StudioUsing SQL Server Management Studio

檢視或變更資料庫的相容性層級To view or change the compatibility level of a database

  1. 連接到適當的 SQL Server Database EngineSQL Server Database Engine執行個體之後,請在 [物件總管] 中按一下伺服器名稱。After connecting to the appropriate instance of the SQL Server Database EngineSQL Server Database Engine, in Object Explorer, click the server name.

  2. 展開 [資料庫] ,然後視資料庫而定,選取使用者資料庫,或者展開 [系統資料庫] 並選取一個系統資料庫。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 以滑鼠右鍵按一下此資料庫,然後按一下 [屬性] 。Right-click the database, and then click Properties.

    [資料庫屬性] 對話方塊隨即開啟。The Database Properties dialog box opens.

  4. [選取頁面] 窗格中,按一下 [選項]In the Select a page pane, click Options.

    目前的相容性層級會顯示在 [相容性層級] 清單方塊中。The current compatibility level is displayed in the Compatibility level list box.

  5. 若要變更相容性層級,請從清單中選取其他選項。To change the compatibility level, select a different option from the list. 選項為 [SQL Server 2008 (100)] 、[SQL Server 2012 (110)] 、[SQL Server 2014 (120)] 、[SQL Server 2016 (130)] 與 [SQL Server 2017 (140)] 。The choices are SQL Server 2008 (100), SQL Server 2012 (110), SQL Server 2014 (120), SQL Server 2016 (130), and SQL Server 2017 (140).

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用 Transact-SQLUsing Transact-SQL

檢視資料庫的相容性層級To view the compatibility level of a database

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫的相容性層級。This example returns the compatibility level of the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
GO  

變更資料庫的相容性層級To change the compatibility level of a database

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會將 AdventureWorks2012AdventureWorks2012 資料庫的相容性層級變更為 120,亦即 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 的相容性層級。This example changes the compatibility level of the AdventureWorks2012AdventureWorks2012 database to 120, which is the compatibility level for SQL Server 2014 (12.x)SQL Server 2014 (12.x).

ALTER DATABASE AdventureWorks2012  
SET COMPATIBILITY_LEVEL = 120;  
GO  

另請參閱See Also

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