May 19, 2010 at 1:44 am
Hi All,
We are migrating our test database from sqlserver 2000 to sqlserver 2008. After migrating the database I set the compatibility level to 100. I got some errors related to non-ANSI joins (*=, =*) so I changed the compatibility level back to 80. My question is If I use the compatibility level 80, Is all features of sql 2008 will be used?? If I put compatibility level 100, will it boost performance of queries and sps? Is the query optimizer generates execution plan based on compatibility level 100?
Thanks in Advance.
May 19, 2010 at 2:22 am
See here
http://technet.microsoft.com/en-us/library/bb510680.aspx
the behaviours influenced by the compatibility level.
As far as I know the compatibility level has nothing to do with performance issues.
However you should review your code in order to make it up to date with SQL 2008.
May 19, 2010 at 2:42 am
Thank you.
May 19, 2010 at 8:17 am
To be able to use all the methods and abilities of 2008, you will need to set the compatibility mode to 2008, not 2000. Performance may or may not be affected, it's actually hard to say what the optimizer will do with the code in compatibility mode, but it will be treated differently, it has to be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply