德瑞克：SQL Server 學習筆記: [SQL Server] View or Change Compatibility Level of Databases, 相容性層級

2017-11-04

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

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

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

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

-- 20_Version_Of_SQL_Server_COMPATIBILITY_LEVEL

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

功能：

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

-- 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

功能：

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

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

-- 05_View all databases compatibility level

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

變更資料庫的相容性層級

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

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