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 ...
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 ...
Alpesh Patel
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
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
ASKER
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 ...
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 ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
ASKER
Thanks - yes my system is SQL2008R2 so I will put 105 level.