Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compatibility level


compatibility level

Author
Message
Erik Hansson
Erik Hansson
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 253
When I upgrade databases from SQL Server 2000 to SQL Server 2005 do I need to change the compatibility level option to SQL Server 2005 after the upgrade?
Is it best practis to change the compatibility level and is it any risk to change the compatibility level?
free_mascot
free_mascot
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2235
Do not change compatibility level unless and untill you thoroughly test it in your test environment.

Keeping the 2000 compatibility won't hurt anything. But if you will change it to 2005 your Stored Procedures MIGHT give some different results as it will take all settings of 2005.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41354 Visits: 18876
The database is structured the same in both cases, but in different modes, it behaves fairly closely to that version in terms of code, keywords, etc. I believe that a 80 mode database could still run newer joins available in 90, but they keywords and hints wouldn't work.

Change it only after you've tested things.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
chileu17
chileu17
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 401
I have wandering around the net for some answers for a while now but I still have the same questions Sad what about performance and things like that? does the compatibility level have a impact on this? and what do you mean by saying that some procedures won't have the same results? :S
Thanks
Paul Mu
Paul Mu
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 391
To see examples of what might break between earlier versions of SQL against 2005, try this link: http://geekswithblogs.net/influent1/archive/2007/01/04/102774.aspx

There is one simple example of getting different results depending on compatibility levels right at the bottom of the page.

Paul
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1401 Visits: 852
I'm currently working on a 2000 to 2005 conversion myself. The biggest thing I have found is that the stored procedures that the vendor wrote do not fully adhere to ANSI standards. For example, I am having to re-write portions of these procs to remove *= and =* in favor of OUTER JOIN. The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.
Paul Mu
Paul Mu
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 391
Which is why it is very important to perform an analysis using the migration wizard as a first step!

Paul
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4099 Visits: 3648
jim.powers (4/29/2008)
The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.


Yes as Paul points out the SQL Server 2005 Upgrade Advisor works very well for this purpose. Also try the BPA (Best Practice Analyzer) as it gives some valuable information as well.

http://msdn2.microsoft.com/en-us/library/ms144256.aspx
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1401 Visits: 852
The Upgrade Advisor is exactly what told me of the problems that would need to be corrected in advance. Our vendor appears to be useless...
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4099 Visits: 3648
Some vendors are like that. But be sure and test thoroughly as there will most likely be things that you encounter that will cause some problems. We have several developers who do not pay attention to column length. SQL 2000 would truncate the data if you tried to insert values larger than the column. SQL 2005 will throw an error. The upgrade advisor will not uncover those types of problems.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search