[SQL Server] View or Change Compatibility Level of Databases, 相容性層級

若要發揮新版本 SQL Server 的功能，調整 資料庫相容性層級 到 目前最新版本 的相容性層級。

相容性層級只會提供與舊版 SQL Server 之間的部分回溯相容性。相容性層級只會影響指定之資料庫的行為，而不會影響整個伺服器的行為。

若要發揮新版本 SQL Server 功能，調整 資料庫相容性層級 到 目前最新版本 的相容性層級。

-- 20_Version_Of_SQL_Server_COMPATIBILITY_LEVEL

---

變更全部資料庫的相容性層級 到 最新版本

功能：

1. 偵測 目前最新版本 的資料庫相容性層級。
2. 變更全部資料庫的相容性層級 到 最新版本。不包含系統資料庫。

-- 01 - View all databases compatibility level
USE master
GO
SELECT name 'DB', compatibility_level FROM sys.databases
GO

-- 02 - Change all databases compatibility level
USE master
DECLARE @cl tinyint, @tsql nvarchar(4000) 
SELECT @cl=compatibility_level FROM sys.databases WHERE name='master'

SET @tsql = (
 SELECT 'ALTER DATABASE ' + name +' SET COMPATIBILITY_LEVEL = ' + CAST(@cl AS varchar(10)) + '; '
 FROM sys.databases
 WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
 FOR XML PATH('') )

EXEC sp_executesql @tsql;
GO

-- 03 - View all databases compatibility level
USE master
GO
SELECT name 'DB', compatibility_level FROM sys.databases
GO

補充：使用 sp_MSforeachdb

-- 02 - Change all user databases compatibility level - sp_MSforeachdb
USE master
DECLARE @cl tinyint, @tsql varchar(4000) 
SELECT @cl=compatibility_level FROM sys.databases WHERE name='master'

SET @tsql = '
 IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
  BEGIN 
   ALTER DATABASE ? SET COMPATIBILITY_LEVEL = ' + CAST(@cl AS varchar(10)) + 
  ' END';

EXEC sp_MSforeachdb @tsql;
GO

---

-- 01_View all databases compatibility level

-- 02_資料庫相容性層級

-- 03_資料庫相容性層級

-- 04_ Change all databases compatibility level

功能：

1. 偵測 目前最新版本 的資料庫相容性層級
2. 變更全部資料庫的相容性層級 到 最新版本。不包含系統資料庫。

-- 補充：Change all user databases compatibility level - sp_MSforeachdb

-- 05_View all databases compatibility level

-- 10_資料庫相容性層級_已調整為140

---

變更 資料庫的相容性層級

在使用者連接到資料庫時變更相容性層級，可能會讓使用中的查詢產生不正確的結果集。
例如，如果在編譯查詢計劃時變更相容性層級，編譯的計畫可能會同時以新的和舊的相容性層級為根據，而導致不正確的計畫以及可能不精確的結果。

此外，如果此計畫放入計畫快取且重複用於後續的查詢，問題可能更嚴重。 若要避免發生不精確的查詢結果，建議您使用下列程序變更資料庫的相容性層級：

1. 使用 ALTER DATABASE SET SINGLE_USER 將資料庫設定為單一使用者存取模式。
2. 變更資料庫的相容性層級。
3. 使用 ALTER DATABASE SET MULTI_USER 將資料庫設定成多使用者存取模式。
4. 如需有關設定資料庫的存取模式的詳細資訊，請參閱ALTER DATABASE (TRANSACT-SQL ).

---

範例程式碼

20171104_Change_Compatibility_Level
https://drive.google.com/drive/folders/1t1UbOLbQqFSylvFb8ZQRB5TTsGbisMfc?usp=sharing

---

ALTER DATABASE (TRANSACT-SQL) 相容性層級
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

SQL Server Version List, Service Pack, Cumulative Updates - 版本清單, 累積更新 - updated to SQL Server 2017
http://sharedderrick.blogspot.tw/2017/10/sql-server-version-list-service-pack.html