SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compatability level


compatability level

Author
Message
charipg
charipg
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4020 Visits: 1193
Hi,

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

thanks in advance.....
DNA_DBA
DNA_DBA
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4717 Visits: 2882
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.
klnsuddu
klnsuddu
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2654 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
charipg
charipg
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4020 Visits: 1193
thanks..........
Ian Scarlett
Ian Scarlett
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9439 Visits: 7232
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!!



charipg
charipg
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4020 Visits: 1193
thanks......
Tom John-342103
Tom John-342103
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 Visits: 312
Does Microsoft have any plans for discontinuing older compatibility levels? If so, where can this information be found, in particular, dates.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)

Group: General Forum Members
Points: 416970 Visits: 47141
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

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


Daveee
Daveee
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1088 Visits: 1033
GilaMonster (3/29/2010)
SQL Server 2009 does not support compatibility level 70 (SQL Server 7)


I'm yet to use this version
GilaMonster
GilaMonster
SSC Guru
SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)SSC Guru (416K reputation)

Group: General Forum Members
Points: 416970 Visits: 47141
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search