?
Solved

Is it worth to change my SQL Server compatibility level?

Posted on 2012-09-20
7
Medium Priority
?
677 Views
Last Modified: 2013-04-08
Dear experts

Before few months I upgrade my database from SQL 2005 to SQL 2008. I do this with deattach in SQL 2005 and attach in SQL 2008 R2. But just now I notice that compatibility level of my new DB 2008 is set to SQL 2005 /90/ . What are exactly real effects? I understand that this applies new T-SQL features, well actually I do not need them, but I’m interesting with speed improvements, reliability and etc.

 I’m also the developer of all system, so I can change the level to 2008 and test everything, but I wonder is it worth? Please share your experience, I’m almost sorry that I do upgrade to SQL 2008 … it require Net 3.5 - HUGE installations on XP /still there many computers with XP/ and almost no additional benefits ...
0
Comment
Question by:dvplayltd
  • 3
  • 3
7 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38421024
If you compatible level is lower than you can not use the features of new version.
0
 
LVL 25

Expert Comment

by:jogos
ID: 38422009
<<well actually I do not need them>>
One day you'll want it and then you will be happy you have changed compability level.

<<but I’m interesting with speed improvements, reliability and etc.>>
using full power of new version is best for good performance
0
 

Author Comment

by:dvplayltd
ID: 38422264
To both

Thanks for your time. If i understand correct, the primary difference is changes in T-SQL machine, right? But the file structure, perfomance is same as for Level 100?

New features ... i will not rewrite my T-SQL commands, this is sure . I prefer to use standard features ... in fact I do not see any better feature that I use in SQL 2008 , only is nice that the backup can be zipped ...
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 38537642
The structure stays the same .

And you don't need to rewrite code because sql is backward compatible, only if already in your sql2005 you were using depricated features then you could encounter it no longer present (from 3 versions back).
'I prefer to use standard features' Yes but standard features in 2008R2 are that of 2008R2 and not the same as for sql2005 or even sql2000

No better features? Some just can be handy ex initializing a variable in the declaration. The MERGE statement? New datatypes date and time?

If you're intrested in performance.  
Read about filtered indexes that can be a real difference in performance.  And when you ever have a tuning session you will find out you cannot use the filterd index because off your compability level.
0
 

Author Closing Comment

by:dvplayltd
ID: 38537716
10x for your time. Probably I'll do a attempt to go to SQL 2008 compatable level ...
0
 
LVL 25

Expert Comment

by:jogos
ID: 38537769
If your system is SQL2008R2 you better set it to 105 (SQL2008R2) and not 100 (SQL2008).
0
 

Author Comment

by:dvplayltd
ID: 38538350
Thanks - yes my system is SQL2008R2 so I will put 105 level.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question