SQL Server 2005 - Compatibility 80 Vs 90

Last Post 02 Apr 2011 10:49 AM by gunneyk. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

01 Apr 2011 06:08 AM

Hi Masters,

    Some of the people that work with me on the company convert a database from SQL Server 2000 to SQL Server 2005 without even changing the compatibility level.

They do not convert anything....

They just do backup on SQL Server 2000 and restore the database on SQL Server 2005.

I always change the compatibility level to 90 and change the error detection from torn page detection to CheckSum, then i reindex the database and update the statistics.

I would like to tell them why is good for them to change the database compatibily level to 90, but i am not choore.   I know that if the database is in compatibility level 80 it works in backword compatibility level, but that affects the performance of the database? or can cause any damage?

I know that only with ctb  level set to 90 you can access some features of SQL Server 2005.

But does 80 affect performance in 2005? or can it cause any problem?

Thank you

New Member
New Member

01 Apr 2011 10:40 AM
Not much on performance and will not damage anything, ensure all codes work in level 90 before changing it.
New Member
New Member

02 Apr 2011 10:49 AM
One key aspect is the newer statistics in the later version. There are enhancements to the stats for the optimizer that wont be available if they are in 80 compat mode.

Acceptable Use Policy