How to assess impact of changing database Compatibility level

I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.

I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?

You can easily change the compatibility level from 80 to 90 using the Properties dialog off the database name in Management Studio, on the Options page. Change the value and SQL Server will make the changes pretty rapidly.

Once you do this, any queries in your stored procedures that use the old outer join syntax (*= and =*) will stop working altogether. Make sure you aren't using that syntax in your application before making the changes. Also, any queries that reference non-existent columns will stop working as well. It's best not to do that anyway.

As far as benefits, you'll be able to run the wealth of dynamic management views (DMVs) against your database, which will allow you to use all the reports in Management Studio Microsoft has provided to evaluate space usage and performance information that are not available when using SQL 2000 compatibility mode.

Does that help?

|||

Yes, that helps.

Looks like a filter through source code will be necessary.

Does SQL Server have a utility to search the db objects (views/stored procs, etc) for this deprecated functionality?

And, if the switch doesn't go smoothly I assume the switch back is just as easy?

Thanks

|||

Check out the SQL Server 2005 Upgrade Advisor Tool it can scan for use of depricated functionality.

You can find the tool on the SQL Server Download Site.

Thanks

Michelle

|||

Hi Allen,

Thanks for the information. We will soon upgrade our databases to SQL 2005, but because of legacy application issues, we will be running in SQL 2000 compatibility mode, at least for the present. I want to use the XML Path syntax in order to concatenate column values, a new feature for SQL 2005. If we run in SQL 2000 compatibility mode, will this feature still be available?

Thanks very much,

Patricia