SQL Server誤區(qū)30日談 第13天 在SQL Server 2000兼容模式下不能使用DMV

誤區(qū) #13.在SQL Server 2000兼容模式下不能使用DMV

錯誤 

     對于兼容模式已經(jīng)存在了很多誤解。80的兼容模式的數(shù)據(jù)庫是否意味著能夠附加或恢復(fù)到SQL Server 2000數(shù)據(jù)庫?當然不是。這只是意味著一些T-SQL的語法，查詢計劃的行為以及一些其它方面和SQL Server 2000中行為一樣（當然，如果你設(shè)置成90兼容模式則和SQL Server 2005中一樣）。

    在SQL Server 2008中，你可以使用ALTER DATABASE SET COMPATIBILITY_LEVEL命令來改變兼容模式,對于SQL Server 2008之前的版本，則使用系統(tǒng)存儲過程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).

    兼容模式對于數(shù)據(jù)庫的實際版本毫無影響,數(shù)據(jù)庫的實際版本會隨著對于數(shù)據(jù)庫的升級而升級，這個升級會阻止更新版本的數(shù)據(jù)庫恢復(fù)或附加到之前的數(shù)據(jù)庫,因為之前版本的實例無法理解新版本數(shù)據(jù)庫的版本。如果想看詳細內(nèi)容，請看我的一篇博文:Search Engine QA #13: Difference between database version and database compatibility level.還有如果當你附加新版數(shù)據(jù)庫到老版本實例時所遇到的錯誤信息:Msg 602, Level 21, State 50, Line 1。

    在SQL Server 2005中設(shè)置為80兼容模式，貌似DMV就不能用了，運行下面代碼創(chuàng)建測試數(shù)據(jù)庫:

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，狀態(tài) 1，第 3 行
'(' 附近有語法錯誤。

    看上去這足以證明80兼容模式不支持DMV。但其實并不是那樣。

    編者:寫到這里之后，我突然意識到我陷入了一個悖論。DMV在80兼容模式下是完全支持的，但不支持的是在80兼容模式下調(diào)用函數(shù)作為DMV的參數(shù)。

    下面是一個可以在80兼容模式下使用函數(shù)作為DMV參數(shù)的技巧，不得不說是神來之筆。那就是在一個90以上兼容模式的數(shù)據(jù)庫下額外調(diào)用80兼容模式下的數(shù)據(jù)庫，看下面代碼:

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數(shù)據(jù)庫工作在80兼容模式下，但上述代碼依然可用。

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

    還有，sys.dm_db_index_physical_stats并不算是一個真正的DMV，而是一個在后臺處理大量信息后返回相關(guān)信息的DMF，因此如果你以NULL作為參數(shù)返回所有的索引信息的話，那代價會非常高昂，你可以看我最近的博文Inside sys.dm_db_index_physical_stats，這篇文章會對細節(jié)和代價進行詳細的解釋。

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

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

    嗯，又揭示了一個誤區(qū)。