Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sandeepvallabhaneni
Starting Member
1 Post |
 Posted - 2010-03-16 : 14:23:26
|
| My production database is in Compatability level 80 and i want to change it to Compatability level 90(I wanted to use some of the additional DMV's for performance tuning).......But most of my stored procedures are build in SQL Server 2000.....Can you guys give me some list of syntax changes made from SQL 2000 to SQL 2005.....What other factor do i need to consider while moving from SQL 2000 to SQL 2005Regards,Sandeep |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 05:25:12
|
| Run the Database Upgrade Advisor to see if you have syntax that is flagged as "suspect"Either way, IMHO, you should run a complete regression test when you change Compatibility Mode. Even if there are no logical differences at all you may well have disastrous query plans.See this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230it is about migrating to SQL 2008 but most is relevant to SQL 2005, and there is a link to an earlier (less complete) thread specific to SQL 2005. In addition to adopting compatibility mode 90 you should rebuild indexes, update statistics, change the PAGE_VERIFY mode, use READ_COMMITTED_SNAPSHOT, and so on as discussed in that post.My advice would be to go straight to SQL 2008. Do you really want to do all this migration work all over again in fairly short order? |
 |
|
|
DanaK
Starting Member
5 Posts |
Posted - 2010-03-18 : 11:27:47
|
| If you switch off 80 to 90 I believe any queries written with ansi joins will no longer work. I inherited an app that was using the *= join's somewhere and the compatibility level change made them no longer work. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 12:27:41
|
Good job too! User OUTER JOIN - much more readable  SQL Documentation has a long list of behavioural changes under the heading "Differences Between Compatibility Level 80 and Level 90" |
|
|
|
DanaK
Starting Member
5 Posts |
Posted - 2010-03-18 : 13:41:08
|
quote:
Originally posted by Kristen
Good job too! User OUTER JOIN - much more readable
Yeah, they were switched to proper joins after that happened. Before I ran into that I never knew you could do a join that way ha. Learn something every day. |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2010-03-23 : 07:18:55
|
quote:
Originally posted by sandeepvallabhaneni
My production database is in Compatability level 80 and i want to change it to Compatability level 90(I wanted to use some of the additional DMV's for performance tuning).......But most of my stored procedures are build in SQL Server 2000.....Can you guys give me some list of syntax changes made from SQL 2000 to SQL 2005.....What other factor do i need to consider while moving from SQL 2000 to SQL 2005Regards,Sandeep
If only reason to change compatibility level is to be able to execute the DMV's, then there isn't any need for that 'coz u can run the DMVs in 80 compatibility mode, just change the current database context to a database with 90 compatibility mode.check this out.[url]http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-about-running-DMVs-on-database-with-lower-compatibility-levels.aspx[/url] |
|
|
|
|
|
|
|
|
Changing compatability level from 80 to 90