Benefits of dbcmptlevel=80 vs 90 ?
-
Hi all,
our customer wants to make upgrade SQL Server from v2000 to v2005. On the testing env. we ensured that application works properly only when main database works under dbcmptlevel=80. Under dbcmptlevel=90 there is no possibility even login to the application. Upgrade advisor shows lot enough incompatibilities. Application vendor could edit code(rewrite SPs etc.) but for additional fees and it will take additional time. So, our client asks me - what advantages would be if dabatase works in the native mode? In the other words - what we would lose if database would stay in 80 compatibility mode? And i can't answer to this question
What could you say about it?
-
You lose the chance to use the new 2005 features. That's about it.
I'm sure there are a few more gotchas than this, but I would not expect a lot (this assumes that you only use the database engine, and not the DTS engine and Analysis services).
-
Maybe exists any list of features which can't to be used in dbcmptlevel=80 ?
The only drawback i noticed that for DB in dbcmptlevel=80 Object Managment Studio doesn't show performance reports(Perf Dashboard doesn't work too). But i suspect that such kind of disadvantages should be more.
My goal is to provide as more as possible proofs that db adaptation under native mode should be done.
-
Check out the Microsoft site for more info, I don't have a link available (maybe someone else will).
The simple fact is that if it worked with 2000, it'll work with 2005 on mode 8.0. As for features, I'd assume that any new 2005 feature will not be available for 8.0, but that just goes without saying.
-
Well not really.
The compatibility level affects several "internal" features but not all SQL 2005 features are lost you can still use CTE row_number etc ...
If you need to know the link here it is:
http://msdn2.microsoft.com/en-us/library/ms178653.aspx
NOTE: I know that some dmv won't work on compatibility level 80 and that is *not* documented
* Noel -
Unfortunatelly document in this link describes which old fashion features won't work under 90 or will work by another manner, but there are no any info about new features which is available under 90 and cannot be used under 80
-
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=368205
About compatability level 80 and 90.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Very similar thread and unfortunatelly that thread last message similar like in this thread:
Thanks Deepak. But it doesn't list any new Sql 2005 features.(Like CTE, CLR Integration and Try.. Catch)
-
Like I already said!!!
CTE, ROW_NUMBER(), CLR and Try...Catch *ARE* available even in 80 compatibility mode.
There are some DMV that are *NOT* usable in 80 but other than that language related features are not dependent of compatibility mode except for the ones mentioned in BOL.
* Noel -
Would moving a database from a SQL 2000 to a SQL 2005 instance, while keeping it at comp. level 80, incur a performance hit (relative to its performance in the SQL 2000 instance?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply