SQL Server誤區30日談 第13天 在SQL Server 2000相容模式下不能使用DMV

誤區 #13.在SQL Server 2000相容模式下不能使用DMV

錯誤

對於相容模式已經存在了很多誤解。80的相容模式的資料庫是否意味著能夠附加或恢複到SQL Server 2000資料庫?當然不是。這隻是意味著一些T-SQL的文法，查詢計劃的行為以及一些其它方面和SQL Server 2000中行為一樣（當然，如果你設定成90相容模式則和SQL Server 2005中一樣）。

在SQL Server 2008中，你可以使用ALTER DATABASE SET COMPATIBILITY_LEVEL命令來改變相容模式,對於SQL Server 2008之前的版本，則使用系統預存程序sp_dbcmptlevel(譯者註：比如sp_dbcmptlevel @dbname='AdventureWorks',@new_cmptlevel=100)，對於這兩種方式如何用，請看:

• 對於SQL Server 2008,BOL入口ALTER DATABASE Compatibility Level
• 對於SQL Server 2005,BOL入口sp_dbcmptlevel (Transact-SQL).

相容模式對於資料庫的實際版本毫無影響,資料庫的實際版本會隨著對於資料庫的升級而升級，這個升級會阻止更新版本的資料庫恢複或附加到之前的資料庫,因為之前版本的執行個體無法理解新版本資料庫的版本。如果想看詳細內容，請看我的一篇博文:Search Engine Q&A #13: Difference between database version and database compatibility level.還有如果當你附加新版資料庫到老版本執行個體時所遇到的錯誤資訊:Msg 602, Level 21, State 50, Line 1。

在SQL Server 2005中設定為80相容模式，貌似DMV就不能用了，運行下面代碼建立測試資料庫:

CREATE DATABASE DMVTest;
GO
USE DMVTest;
GO
CREATE TABLE t1 (c1 INT);
CREATE CLUSTERED INDEX t1c1 on t1 (c1);
INSERT INTO t1 VALUES (1);
GO

EXEC sp_dbcmptlevel DMVTest, 80;
GO

SELECT * FROM sys.dm_db_index_physical_stats (
DB_ID ('DMVTest'), -- database ID
OBJECT_ID ('t1'), -- object ID <<<<<< Note I'm using 1-part naming
NULL, -- index ID
NULL, -- partition ID
'DETAILED'); -- scan mode
GO

你會得到如下報錯資訊:

訊息 102，層級 15，狀態 1，第 3 行
'(' 附近有語法錯誤。

看上去這足以證明80相容模式不支援DMV。但其實並不是那樣。

編者:寫到這裡之後，我突然意識到我陷入了一個悖論。DMV在80相容模式下是完全支援的，但不支援的是在80相容模式下調用函數作為DMV的參數。

下面是一個可以在80相容模式下使用函數作為DMV參數的技巧，不得不說是神來之筆。那就是在一個90以上相容模式的資料庫下額外調用80相容模式下的資料庫，看下面代碼:

USE master
SELECT * FROM sys.dm_db_index_physical_stats (
DB_ID ('DMVTest'), -- database ID
OBJECT_ID ('DMVTest..t1'), -- object ID <<<<<< Note I'm using 3-part naming here now
NULL, -- index ID
NULL, -- partition ID
'DETAILED'); -- scan mode
GO

雖然DMVTest資料庫工作在80相容模式下，但上述代碼依然可用。

但是有一點值得注意的是,你一定要保證Object參數的正確,如果你僅僅讓第二個參數還是OBJECT_ID ('t1'), 那麼這個函數會嘗試在Master資料庫中找表t1,正常來說這就會返回NULL，這就導致剛才那個DMV以NULL作為參數，從而返回了所有DMVTest表下的索引狀態.而如果Master表中也有一個DMV，那就更不幸了，你將得到錯誤的資訊。

還有，sys.dm_db_index_physical_stats並不算是一個真正的DMV，而是一個在幕後處理大量資訊後返回相關資訊的DMF，因此如果你以NULL作為參數返回所有的索引資訊的話，那代價會非常高昂，你可以看我最近的博文Inside sys.dm_db_index_physical_stats，這篇文章會對細節和代價進行詳細的解釋。

還有一種在80相容模式下使用DMV的方式是不再DMV中以函數作為參數，而是傳變數進去，代碼如下:

DECLARE @databaseID INT;
DECLARE @objectID INT;

SELECT @databaseID = DB_ID ('DMVTest');
SELECT @objectID = OBJECT_ID ('t1');

SELECT * FROM sys.dm_db_index_physical_stats (
@dbid, -- database ID
@objid, -- object ID
NULL, -- index ID
NULL, -- partition ID
'DETAILED'); -- scan mode
GO

嗯，又揭示了一個誤區。