Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

compatability level Expand / Collapse
Author
Message
Posted Tuesday, June 30, 2009 12:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 412, Visits: 1,015
Hi,

what is cmpt level?
why should we change cmpt level if we move database from sql2000 to sql2005 ?

thanks in advance.....
Post #744192
Posted Tuesday, June 30, 2009 1:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 2,634, Visits: 2,236
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.
Post #744220
Posted Tuesday, June 30, 2009 2:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 10:33 AM
Points: 282, Visits: 757
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
Post #744221
Posted Wednesday, July 1, 2009 12:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 412, Visits: 1,015
thanks..........
Post #745095
Posted Wednesday, July 1, 2009 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 1,327, Visits: 4,506
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!!



Post #745168
Posted Thursday, July 2, 2009 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 412, Visits: 1,015
thanks......
Post #746265
Posted Monday, March 29, 2010 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 6:48 AM
Points: 63, Visits: 220
Does Microsoft have any plans for discontinuing older compatibility levels? If so, where can this information be found, in particular, dates.
Post #891964
Posted Monday, March 29, 2010 2:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #892176
Posted Tuesday, March 30, 2010 9:00 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 7:32 PM
Points: 700, Visits: 1,029
GilaMonster (3/29/2010)
SQL Server 2009 does not support compatibility level 70 (SQL Server 7)


I'm yet to use this version
Post #892889
Posted Tuesday, March 30, 2010 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
Please note that was a mistype and should have read SQL Server 2008.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #892909
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse