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

Question

I plan to upgrade an instance of SQL Server 2000 to an instance of SQL Server 2005.

I will not change the database code, so I will not use any SQL2005 syntax. Is there a reason to set my compatibility higher (to level 9)? Or is it okay to leave compatibility level 8?

Answer 1

There are two ways to look at this:

• If you want to change your code (or install some collaborative SQL2005 / 2008) in the future, your compatibility mode will affect this decision.
• If you did not run the Upgrade Advisor, you may not be able to update :) compatibility level 9 disables certain syntaxes and functions available in 8.

So, the simple answer is: no, you do not need to increase the level of compatibility, but then you should also think about your future with this field :)

Answer 2

Its fine to leave him at 8 until you want to upgrade.

Answer 3

Only a small point - compatibility level 90 lowers support for the old ANSI-89 outer join syntax ( *= , =* ). If you decide to switch, be sure to check your code base first.

Answer 4

The well reports you get at level 90 are very good ... this in itself is an incentive to upgrade ...

Answer 5

The compatibility level is not just a matter of preference, but a question about how you use the database, you can check what has changed on this link:

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

Therefore, if you do not know that changing the compatibility level will not cause a breakdown, you should definitely upgrade. So check carefully before taking any chances.