As SQL Server 2005 hit the market it did so with some impressive new features and functionality. One of the new features was to allow Database Administrators to set the SQL Server Compatibility Level of a database. This is useful if you are relying on a feature in an older version of SQL Server that is no long supported in SQL Server 2005. It is also a good safe guard for migrating a database application forward to the latest and greatest version of SQL Server if the application was not certified on newer versions of SQL Server. The Compatibility mode feature is also in SQL Server 2008.
One issue that I've encountered with the Compatibility mode is that if you are restoring a database from a back up of an older version of SQL Server the Compatibility Level will automatically be set to the older version of the database. For example I have a test database that I use with ProjectWise. When I took that database from SQL Server 2000 and moved it to SQL Server 2005 the compatibility mode was set to SQL Server 2000. In my case I really wanted to move the database forward to SQL Server 2005 to ensure that I was testing against the latest syntax and feature set of SQL Server.
It is very easy to check and set the compatibility mode in SQL Server 2005 and SQL Server 2008. To do this go into SQL Server Management Studio. Then Right click on the database in question and choose Properties. Next go under Options and check and/or set the Compatibility Level.
The oversight of this setting recently left me confused as to why I was getting a syntax error in a SQL statement I was trying to execute. I was trying to find the SQL Text for the cached execution plans that were used the most. In SQL Server 2005 the following SQL statement will give you these counts as well as the text for the SQL Statement.
select TOP 100 objtype, LEFT([sql].[text], 100) as [text],usecounts from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY usecounts DESC
However this same statement fails in SQL Server 2000 or in a database where the compatibility level is set to SQL Server 2000. There are a few reasons this statement fails when the SQL Server Compatibility Level is set to SQL Server 2000. The first is that sys.dm_exec_cached_plans dynamic management view. This is new in SQL Server 2005 and is an equivalent to the syscacheobjects system table in SQL Server 2000. The second reason is that the Table-valued function sys.dm_exec_cached_plans is also new in SQL Server 2005 and replaces the system level function fn_get_sql.
The Equivalent statement for SQL Server 2000 is:
select objtype, sql, usecounts from syscacheobjects ORDER BY usecounts DESC
If you find that you have SQL Statements that work on one database, but not another it could be due to a discrepancy with the SQL Server Compatibility Level.
So are you really using SQL Server 2005?