Should I change my upgraded database's compatibility level from 8 to 9?

Question

I am planning to upgrade a SQL Server 2000 instance to a SQL Server 2005 instance.

I am not going to change any database code, so I won't be taking advantage of any SQL2005 specific syntax. Is there any reason to set my compatibility higher (to level 9)? Or is it ok to leave at compatibility level 8?

Answer 1

I'd be forced to wonder why are you upgrading, if that is the case?

Answer 2

Its fine to leave it at 8 until you wish to upgrade.

Answer 3

There are two ways to look at this:

• If you want to change your code (or install some collaborating SQL2005/2008 servers) in the future, your compatibility mode will affect that decision
• If you haven't run the Upgrade Adviser, you may not be able to upgrade :) compatibility level 9 disables certain syntaxes and features that were available in 8.

So, simple answer is no you don't have to increase your compat level, but then you've to consider your future with this box too :)

Answer 4

Just a small point - compat level 90 drops support for the old ANSI-89 outer join syntax (*=, =*). If you do decide to switch, make sure you have checked your code base for those first.

Answer 5

Well Reports that u get with level 90, is very nice... that alone is incentive to upgrade...

Answer 6

Compatibility level is not just a question of preference, its a matter of how you use the database you can check what changed on this link:

http://msdn.microsoft.com/en-us/library/bb510680.aspx

So unless you know that changing the compatibility level will not break anything you should definetly upgrade. So do a thoroughly check before taking any chances.