Database compatibility and new features

Have you attempted to execute queries using some of the new keywords and constructs only to see error messages like the following?

Msg 321, Level 15, State 1, Line 5

"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Well, the error message is correct. You cannot make use of several new OPERATORS, KEYWORDS and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level. Let’s walk through an example to understand what we are discussing here. Please do not execute the following scripts directly on your production server.

SCRIPT 1

EXEC sp_dbcmptlevel 'master' , 80

GO

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 - s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

SCRIPT 2

EXEC sp_dbcmptlevel 'master' , 90

GO

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 - s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

Both the scripts do the same work except that they operate under different compatibility levels. The first script will encounter the error shown above. The second script will get you the needed output. The thought that will come into your mind immediately: I want to find out who changed the compatibility level of master database on my server with my written permission! Good luck, finding that person.

So, here is what happens. Right after you perform an in-place upgrade of a SQL Server 2000 server to SQL Server 2005 server, the compatibility level of various databases is as shown below.

But, if you perform a fresh install of SQL Server 2005, the compatibility level of various databases is as shown below:

Based on this info, you can observe that when we upgrade a server or upgrade an existing database from SQL Server 2000 to SQL Server 2005, user databases and master database retain their compatibility setting. All new databases will be created at the default compatibility level dictated by model database compatibility level. All of this is documented in the Books Online topic sp_dbcmptlevel (Transact-SQL). If you performed an upgrade of SQL Server 7.0 to SQL Server 2000, you might have noticed a different behavior. The details are documented in the Books Online topic SQL Server 2000 and SQL Server version 6.5.

The behavior presented in the upgrade from SQL Server 2000 to SQL Server 2005 is based on a conscious decision taken by the SQL Server product group. . The main motivation behind this is to provide a smooth upgrade process and to ensure existing applications work without encountering a problem after the upgrade. You would question why this decision applies to master database as well. Even though the best practice is not to keep user objects in the master database, many internal features and several applications in the market keep some vital information in the master database. To ensure none of this encounter a problem, the master database is also left at compatibility level 80 after an upgrade. This obviously now poses a problem for application and scripts that wants to execute in the context of the master database as well as use some of the new keywords or operators. They will now run into the problems and errors that we discussed early on.

The general policy going forward is as follows:

1. New databases are always created with the new compatibility level (unless model has a different compatibility level)

2. Old databases on upgrade/attach/restore retain the compatibility level. If the compatibility level is no longer supported then we bump it up to the lowest supported compatibility level.

If you run into problems because of this behavior:

- After complete evaluation and testing, you could decide to change the compatibility level of master to 90. [Or whatever is the default level of that version of SQL Server].

- Execute these scripts from a database context other than master. For example, you could choose to use tempdb.

If you have situations where this is posing a lot of trouble for you, please provide feedback to the SQL Server Development team using the Microsoft Connect website. We are working to come up with mechanism where the current policy can still exist and also allow for usage of the new operators, keywords and constructs. We have heard from customers that this behavior is causing some SQL Server tools shipped with the product to run into issues.

Thanks

Suresh B. Kandoth

SQL Server Escalation Engineer