SQL Server myths 30th talk about the 13th day you cannot use Dmv_mssql in SQL Server 2000 compatibility mode

Misunderstanding #13. The DMV cannot be used in SQL Server 2000 compatibility mode

Error  

There has been a lot of misunderstanding about compatibility mode. Does the 80 compatibility mode database imply the ability to attach or recover to a SQL Server 2000 database? Of course not. This simply means some T-SQL syntax, the behavior of the query plan, and some other aspects as in SQL Server 2000 (of course, if you set it to 90 compatibility mode, as in SQL Server 2005).

In SQL Server 2008, you can use the Alter DATABASE SET compatibility_level command to change the compatibility mode and use system stored procedures for versions prior to SQL Server 2008 sp_dbcmptlevel (Translator Note: For example sp_dbcmptlevel @dbname = ' AdventureWorks ', @new_cmptlevel =100), for these two ways how to use, see:

• For SQL Server 2008,bol portal alter DATABASE compatibility level
• For SQL Server 2005,bol Portal sp_dbcmptlevel (Transact-SQL).

Compatibility mode has no effect on the actual version of the database, and the actual version of the database is upgraded with the upgrade of the database, which prevents the updated version of the database from being restored or attached to the previous database, because the previous version of the instance cannot understand the version of the new version of the database. If you want to see more details, see my blog post: Search Engine q&a #13: Difference between database version and database compatibility level. And if you attach The error message that was encountered when adding the new database to the old version instance: MSG 602, Level, 1.

In SQL Server 2005, set to 80 compatibility mode, it seems that the DMV is not available, run the following code to create a test database:

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

You will receive the following error message:

Message 102, Level 15, State 1, line 3rd
' (' There is a grammatical error nearby.

It seems to be enough to prove that the 80 compatibility mode does not support the DMV. But it's not like that.

Editor: After writing here, I suddenly realized that I was caught in a paradox. The DMV is fully supported in 80 compatibility mode, but it does not support calling functions as parameters of the DMV in 80 compatibility mode.

Here's a technique to use a function as a DMV parameter in 80 compatibility mode, which has to be said to be genius. That is, in a database of more than 90 compatibility mode, the additional call to the 80 compatibility mode database, look at the following code:

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
NULL,--Index ID
NULL,--Partition ID
' DETAILED '); --Scan mode
Go

Although the Dmvtest database works in 80 compatibility mode, the above code is still available.

But it's worth noting that you have to make sure that the object argument is correct, if you just let the second argument be object_id (' T1 '), then the function will try to find the table T1 in the master database, which normally returns null. This causes the DMV to return the index status under all dmvtest tables by using NULL as a parameter. And if there's a DMV in the master table, it's even more unfortunate that you'll get the wrong message.

Also, sys.dm_db_index_physical_stats is not really a DMV, but a DMF that returns relevant information after processing a large amount of information in the background, so if you return all the index information with NULL as a parameter, it will be very expensive. You can see my recent blog inside sys.dm_db_index_physical_stats, this article will explain the details and the cost of a detailed explanation.

Another way to use the DMV in 80 compatibility mode is to stop using functions as arguments in the DMV, instead of passing variables in, the code reads as follows:

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

Well, it also reveals an erroneous zone.