Is it worth to change my SQL Server compatibility level?

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 ...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpesh PatelAssistant ConsultantCommented:
If you compatible level is lower than you can not use the features of new version.
<<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
dvplayltdAuthor Commented:
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 ...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dvplayltdAuthor Commented:
10x for your time. Probably I'll do a attempt to go to SQL 2008 compatable level ...
If your system is SQL2008R2 you better set it to 105 (SQL2008R2) and not 100 (SQL2008).
dvplayltdAuthor Commented:
Thanks - yes my system is SQL2008R2 so I will put 105 level.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.