About SQL Server misunderstanding on the last 30 days DMV cannot be used in SQL Server 13th compatibility mode

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

Error

There are many misunderstandings about the compatibility mode. Does the database in the compatible mode of 80 mean that it can be attached or restored to the SQL Server 2000 database? Of course not. It just means some T-SQL syntaxes, the behavior of the query plan and some other aspects are the same as the behavior in SQL Server 2000 (of course, if you set the compatibility mode to 90, it is the same as in SQL Server 2005 ).

In SQL Server 2008, you can use the alter database set COMPATIBILITY_LEVEL command to change the compatibility mode. For versions earlier than SQL Server 2008, use the system storage process sp_dbcmptlevel: for example, sp_dbcmptlevel @ dbname = 'adventureworks' and @ new_cmptlevel = 100). For how to use these two methods, see:

• For SQL Server 2008, the Boolean entry ALTER DATABASE Compatibility Level
• For SQL Server 2005, enter sp_dbcmptlevel (Transact-SQL) as the BOL entry ).

The compatibility mode has no impact on the actual database version. The actual database version will be upgraded with the Database Upgrade. This upgrade will prevent the database of the updated version from being restored or attached to the previous database, because instances of the previous version cannot understand the version of the new database version. For more information, see A blog: Search Engine Q & A #13: Difference between database version and database compatibility level. if you attach a new database to an instance of the old version, the following error occurs: Msg 602, Level 21, State 50, Line 1.

In SQL Server 2005, if the mode is set to 80 compatible, it seems that DMV cannot be used. 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
'Detailled'); -- scan mode
GO

You will get the following error message:

Message 102, Level 15, status 1, 3rd rows
'(' There is a syntax error nearby.

It seems that this is enough to prove that the 80 compatibility mode does not support DMV. But that is not the case.

Editor: After writing this article, I suddenly realized that I was in a paradox. DMV is fully supported in the 80 compatible mode, but it does not support calling a function as the DMV parameter in the 80 compatible mode.

The following is a technique for using functions as DMV parameters in 80 compatible mode. That is, an additional 80-compatible database is called under a database of more than 90 compatibility modes. See 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 here now
NULL, -- index ID
NULL, -- partition ID
'Detailled'); -- scan mode
GO

Although the DMVTest database works in the 80 compatible mode, the above Code is still available.

However, you must ensure that the Object parameter is correct. If you only want the second parameter to be OBJECT_ID ('t1 '), then this function will try to find table t1 in the Master database. Normally, this will return NULL, which causes the DMV to take NULL as the parameter, the index status of all DMVTest tables is returned. if the Master table also has a DMV, you will get the error message.

Also, sys. dm_db_index_physical_stats is not a real DMV, but a DMF that processes a large amount of information in the background and returns the relevant information. Therefore, if you use NULL as the parameter to return all the index information, the cost will be very high. You can refer to my recent blog, Inside sys. dm_db_index_physical_stats. This article will explain the details and costs in detail.

Another way to use DMV in the 80 compatible mode is to transfer the variable instead of using the function as the parameter in DMV. The Code is 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
'Detailled'); -- scan mode
GO

Well, it reveals another misunderstanding.