June 30, 2009 at 12:21 am
Hi,
what is cmpt level?
why should we change cmpt level if we move database from sql2000 to sql2005 ?
thanks in advance.....
June 30, 2009 at 1:58 am
As new versions of SQL Server are released, certain features or ways of doing things change. So something that worked in an older version of SQL Server may not work in a newer version or may behave differently. The compatibility level is a way of telling SQL Server to use the older methods of doing things and is applied at a database level. So if the compatibility level is 90 then it will use all the 2005 options, if it is 80 it will allow 2000 features that have been removed from 2005. The compatibility option does not affect the version of the database - so a 2005 database cannot be restored into 2000 as it is still a 2005 database even if the compatibility level is 80. Also, it does not stop you using features that are above the compatibility level you have set - so if you were to set the level to 8 then 2005 features would still be available to you.
As for changing the level after moving a database, it is advisable to keep it at the current level until you have tested the application with the higher level.
June 30, 2009 at 2:02 am
what is cmpt level?
why should we change cmpt level if we move database from sql2000 to sql2005 ?
cmpt is the database compatibility level.
70 for sql server 7.0
80 for sql server 2000
90 for sql server 2005
100 for sql server 2008.
The compatibility level has to be changed from 80 to 90 when you upgrade from sql server 2000 to sql server 2005 to fully use the new T-SQL features.
You also need to run upgrade adviser before upgrading to sql server 2005, which gives what are things you need to change before upgrade and after upgrade. Accordingly you to change your T-sql code.Some t-sql commands will not be run after you change the cmpt level to 90, which need to modify later.
thanks
July 1, 2009 at 12:03 am
thanks..........
July 1, 2009 at 2:29 am
DNA (6/30/2009)
Also, it does not stop you using features that are above the compatibility level you have set - so if you were to set the level to 8 then 2005 features would still be available to you.
Not 100% true, it does stop you from doing some things e.g. UDF's weren't available in SQL Server 7, and if you set compatibility to 70 and try to create a function, you get a syntax error.
But, CTE's were introduced in SQL 2005, and if you try to use them with compatibility set to 80, it works!!
July 2, 2009 at 7:37 am
thanks......
March 29, 2010 at 9:36 am
Does Microsoft have any plans for discontinuing older compatibility levels? If so, where can this information be found, in particular, dates.
March 29, 2010 at 2:00 pm
Yes, but it's not date-based.
SQL Server 2005 does not support compatibility level 65 (SQL Server 6.5)
SQL Server 2008 does not support compatibility level 70 (SQL Server 7)
p.s. Please in future post new questions in a new thread rather than resurrecting an old thread.
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
March 30, 2010 at 9:00 am
GilaMonster (3/29/2010)
SQL Server 2009 does not support compatibility level 70 (SQL Server 7)
I'm yet to use this version
March 30, 2010 at 9:13 am
Please note that was a mistype and should have read SQL Server 2008.
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
March 30, 2010 at 10:01 am
Thanks Gail.
So, it seems reasonable to assume that the next SQL version after SQL2008 will drop support for 2000 compatibility.
September 12, 2012 at 9:44 am
I have just came across this compatibility issue in my new company. The original DB was created SQL 7. or compatibility 70
My question is if there isn't any issues with there code, would it be an issue to raise the compatibility level to the current version we are running 2008 r2?
I went back to my new boss and he said this note below. I wanted to know if this is true or not as this is the first time I have came across this type of issue
"when transitioning to SQL 2005 that if the database was created in earlier versions it would never truly reach the latter compatibility level unless the database was natively created with that version"
Basically raising the compatibility level will I be able to use most of the new functionality in the newer versions that we have upgraded to? Thanks
September 12, 2012 at 9:58 am
D-SQL (9/12/2012)
"when transitioning to SQL 2005 that if the database was created in earlier versions it would never truly reach the latter compatibility level unless the database was natively created with that version"
That is not correct.
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
September 12, 2012 at 10:08 am
Ok Thanks I will move to raise the level so I can use the new functionality. Thanks for your help
September 12, 2012 at 10:17 am
You can use most of the new functionality without raising the compat level. Compat level's about preserving old behaviour, not preventing new.
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply