June 27, 2012 at 8:13 am
While just about all my servers are up to SQL Server 2008 or 2008R2, we have many databases still operating in 8.0 compatability mode. for a while, I've had this in my head as a "to do"- to make the arguement that it's time to come up to 10.0 compatablity mode. As I started documenting benefits of doing so, I surprised myself by how few real benefits I was able to come up with- and even less supporting documentation.
If anyone could help add to my list of benefits that coming up to 10.0 compataiblityu would provide, that would be appreciate. Here's what I have thus far (worth noting that I havent tested or played with any of this, so if Im wrong feel free to correct me):
-Comparisons between numeric types are compared without coversion, resulting in a performance increase
-Filtered indexes are available
-New Date data type is availabe to reudece storage
-page and table compression are available-partitioning works and parralleism on partiions works
-sparse columns are available
-while many DMVs are available at the instance level, some database level DMVs like index_phsyical_stats are much more efficient in native compatability
-compatabilty is only supported 2 levels back. 8.0 will not be supported on 2012 (this is not a strong arguement since we are not going to 2012 for quite a while)
June 27, 2012 at 9:31 am
Personally, I think the main benefit is that the compatibility will disappear and you need to make sure that your code can run on new versions. It is the things that become deprecated that are issues, not necessarily new functionality.
Jared
CE - Microsoft
June 27, 2012 at 9:40 am
SQLKnowItAll (6/27/2012)
Personally, I think the main benefit is that the compatibility will disappear and you need to make sure that your code can run on new versions. It is the things that become deprecated that are issues, not necessarily new functionality.
Yeah, that doesnt help me much because I will get " 2012 is a long way off for us, what is the benefit of doing this now"
June 27, 2012 at 11:12 am
A note: If you are running in compat 80 on a SQL 2008 R2 Enterprise instance, you can still use compression for your tables, views (indexed views), and indexes.
The biggest benefit is the improved efficiency of the DMVs IMO.
Compat 80 does not exist in 2012, and even if it is a long way off, I'd rather do it sooner than later.
What real benefit is there to keeping a database in Compat 80? The money was spent on SQL 2008, why not use what you paid for?
One thing you did not list that I am not sure works in Compat 80 is backup compression.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 12:19 pm
SQLRNNR (6/27/2012)
One thing you did not list that I am not sure works in Compat 80 is backup compression.
It does. So do most 2008 features. The only thing that compat mode affects is how some query constructs are treated by the query parser and query execution engine.
Cross apply to a function doesn't work in all cases under compat mode 80 for example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2012 at 12:49 pm
Thanks Gail. I was sure it would work as you said.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 1:41 pm
Gail- I dont suppose you know where I could find detailed information about the differences in how the query processor treats dbs in 8.0 compatability- besides the syntax related differences?
June 27, 2012 at 2:01 pm
Books Online, the deprecation pages. Probably about the best.
It is about syntax, how it's interpreted, what's allowed and what's not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply