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

    嗯，又揭示了一个误区。