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

錯(cuò)誤 

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

    在SQL Server 2008中，你可以使用ALTER DATABASE SET COMPATIBILITY_LEVEL命令來改變兼容模式,對(duì)于SQL Server 2008之前的版本，則使用系統(tǒng)存儲(chǔ)過程sp_dbcmptlevel(譯者注：比如sp_dbcmptlevel @dbname='AdventureWorks',@new_cmptlevel=100)，對(duì)于這兩種方式如何用，請(qǐng)看:

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

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

    在SQL Server 2005中設(shè)置為80兼容模式，貌似DMV就不能用了，運(yùn)行下面代碼創(chuàng)建測(cè)試數(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

    你會(huì)得到如下報(bào)錯(cuò)信息:

消息 102，級(jí)別 15，狀態(tài) 1，第 3 行
'(' 附近有語法錯(cuò)誤。

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

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

    下面是一個(gè)可以在80兼容模式下使用函數(shù)作為DMV參數(shù)的技巧，不得不說是神來之筆。那就是在一個(gè)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兼容模式下，但上述代碼依然可用。

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

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

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

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

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