InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 21:41:40 GMT20http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI think the same idea applies to the SQL Native client. Everything is still SQL 2005. When you set the compatability level of a database to be SQL 2000 running on SQL 2005, it merely emulates the behavior of a SQL 2000 database. But the database still resides on a SQL 2005 instance.Wed, 30 Apr 2008 12:11:17 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxYour English is fine. It is probably more the medium that we are using. Same problem with email. Good luck with what you are doing. If you have a link to that article feel free to include it and I will try to decipher it. :PWed, 30 Apr 2008 12:07:18 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxhey what about SQL Native Client??? does changing the compatibiliy level affect this in any way? ThanksWed, 30 Apr 2008 12:05:27 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI am sorry but my English is not very good anymore :hehe: but you just keep guessing what I am trying to say lol Thanks again for answering so soon. Yeah I was talking about the client connections that in SQL Server are not the same as in Sql 2k. I had probably misunderstood what I read in that article :D lol thanks for the explanation anyways.Wed, 30 Apr 2008 12:02:02 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI am not sure I understand what you mean by upgrading your clients connections, but yes the mechaninsm that you use to connect to the database must be compatible with SQL 2005 since that is what you are connecting to. Changing the compatability level of a user database does not change that. Your system databases and SQL Server's application code is still SQL 2005.Wed, 30 Apr 2008 11:24:51 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxSo I would have to upgrade my clients applications connections right? in order to make them compatible with sql server 2005?Wed, 30 Apr 2008 10:36:07 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxAfter re-reading your question, perhaps I should add that you can only connect to SQL 2005 using the methods that SQL 2005 supports. Changing the compatibility level of one of your user databases to level 80 would not effect those requirementsWed, 30 Apr 2008 08:39:16 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxNo it should not effect your network connections. You are still connecting to the SQL 2005 database engine. By setting the compatibility level to 80 on your database you are just ensuring that your database still behaves like SQL 2000.Wed, 30 Apr 2008 08:33:32 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxThanks for that quick answer Cliff. But I have one last question to fire :DWhat about the clients?Does changing the compatibility level affect the clients in terms of network connections? I mean like network protocols that are not supported "anymore". I read somewhere that changing the compatibility level in 70, I wouldn't have to update my clients connections which would be an advantage I guess.Thanks again for the help.Wed, 30 Apr 2008 08:22:51 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxYes, generally they are complex quereies involving both inner and outer joins. SQL 2000 would join the Inner Join tables first since they were the most likely to reduce the result set. SQL 2005 does not necessarily join the tables in that order since sometimes the outer joined tables may be very small. But sometimes when the optimizer chooses to perform the outer join first, the result can be a cartesian product. You will see a No Join Predicate warning in the execution plan and high CPU utilization with LOTS of reads. There is a known bug (#50001820) that is similar to this that was fixed in SP 2, CU 4 but it does not address all occurances of this problem. Adding a Force Order hint on SQL 2005 results in a better execution plan for these queries.Tue, 29 Apr 2008 09:33:31 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspx[quote][b]Cliff Jones (4/29/2008)[/b][hr][quote][b]jim.powers (4/29/2008)[/b][hr]Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.[/quote]Actually, that is what we did as well since we had to have a version that was backwards compatable to SQL 2000 but that we certified to run on SQL 2005. So that is not uncommon. Since it had to be backwards compatable we could not use any SQL 2005 features and it had to run compatibility mode 80.Generally we find that performance over all is better on SQL 2005 but we do find several queries that run considerably slower on SQL 2005. We have opened up incidents with Microsoft on these and they admit there are some issues with complex queries involving outer joins. We have had to add some Force Order query hints to resolve them until we can rewrite the queries.[/quote]Hi Cliff, you said that some queries run slower in SQL 2005, what kind of queries are they? and do you have any ideas why is that? I am a developer and would be thrilled to know about the things I can do better :DThanks.Tue, 29 Apr 2008 09:14:35 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI believe that certifying a product can run in 2005 only in compatability mode is so companies can move forward with upgrades. Some products will not run in 2005 because of the changes in the way 2005 did security among other things. In compatability mode SQL server still functions in the same manner. Some vendors have not changed their applications to function properly in 2005 mode. I would find out what the vendor is planning when 2008 is released.Tue, 29 Apr 2008 08:42:07 GMTQ -631159http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxWe are the vendor for our product. Our requirement was that the product still had to run on SQL 2000 and also on SQL 2005. We discovered through testing that the behavior of certain queries was not exactly the same on level 90 as level 80 which forced us to run on SQL 2005 on level 80 for one version. As a vendor you always have new customers and early adopters that want to run on SQL 2005 and a few customers you have to drag kicking and screaming to the new platform. They are the lowest common denominator . So you have to create an upgrade path that allows the late adopters to get there. Tue, 29 Apr 2008 08:38:17 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxWhat's the point of that! Unless they are not 100% sure that everything will work in 90 mode? Don't trust your vendors and fully test your system yourself I would say :PTue, 29 Apr 2008 08:26:49 GMTPaul Muhttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspx[quote][b]jim.powers (4/29/2008)[/b][hr]Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.[/quote]Actually, that is what we did as well since we had to have a version that was backwards compatable to SQL 2000 but that we certified to run on SQL 2005. So that is not uncommon. Since it had to be backwards compatable we could not use any SQL 2005 features and it had to run compatibility mode 80.Generally we find that performance over all is better on SQL 2005 but we do find several queries that run considerably slower on SQL 2005. We have opened up incidents with Microsoft on these and they admit there are some issues with complex queries involving outer joins. We have had to add some Force Order query hints to resolve them until we can rewrite the queries.Tue, 29 Apr 2008 08:22:59 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxInterestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.Tue, 29 Apr 2008 08:14:53 GMTjim.powershttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxWhen we upgraded from 2000 to 2005 we tested running everything in 2000 compatability mode. We did not see any degredation in performance. I will concede that we do not have tera byte databases, but we do have some databases in the double and triple didget gigabyte range. We have moved internal databases to 2005 after completing thorough testing in development. We wait until vendors certify their software on 2005 before moving their databases. We still perform testing in development first. The major reason for waiting for vendors to certify is that they generally will not support the product if there are issues and they have not certified.Tue, 29 Apr 2008 08:10:30 GMTQ -631159http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxSome 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.Tue, 29 Apr 2008 07:51:15 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxThe Upgrade Advisor is exactly what told me of the problems that would need to be corrected in advance. Our vendor appears to be useless...Tue, 29 Apr 2008 07:37:52 GMTjim.powershttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspx[quote][b]jim.powers (4/29/2008)[/b][hr]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.[/quote]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 Tue, 29 Apr 2008 07:35:01 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxWhich is why it is very important to perform an analysis using the migration wizard as a first step!PaulTue, 29 Apr 2008 07:23:38 GMTPaul Muhttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI'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.Tue, 29 Apr 2008 07:01:40 GMTjim.powershttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxTo 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.aspxThere is one simple example of getting different results depending on compatibility levels right at the bottom of the page.PaulMon, 28 Apr 2008 09:48:35 GMTPaul Muhttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxI have wandering around the net for some answers for a while now but I still have the same questions :( 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 ThanksMon, 28 Apr 2008 09:30:05 GMTchileu17http://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxThe 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.Mon, 28 Apr 2008 08:50:52 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxDo 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.Mon, 28 Apr 2008 06:00:50 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic491295-146-1.aspxWhen 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?Mon, 28 Apr 2008 05:35:16 GMTErik Hansson