SQLServerCentral.com / Administering / SQL Server 2005 / compatability level / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:10:40 GMT20RE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxYou can use most of the new functionality without raising the compat level. Compat level's about preserving old behaviour, not preventing new.Wed, 12 Sep 2012 10:17:30 GMTGilaMonsterRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxOk Thanks I will move to raise the level so I can use the new functionality. Thanks for your helpWed, 12 Sep 2012 10:08:28 GMTD-SQLRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspx[quote][b]D-SQL (9/12/2012)[/b][hr]"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"[/quote]That is not correct.Wed, 12 Sep 2012 09:58:16 GMTGilaMonsterRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxI have just came across this compatibility issue in my new company. The original DB was created SQL 7. or compatibility 70My 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? ThanksWed, 12 Sep 2012 09:44:26 GMTD-SQLRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxThanks Gail.So, it seems reasonable to assume that the next SQL version after SQL2008 will drop support for 2000 compatibility.Tue, 30 Mar 2010 10:01:41 GMTTom John-342103RE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxPlease note that was a mistype and should have read SQL Server 2008.Tue, 30 Mar 2010 09:13:23 GMTGilaMonsterRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspx[quote][b]GilaMonster (3/29/2010)[/b][hr]SQL Server 2009 does not support compatibility level 70 (SQL Server 7)[/quote]I'm yet to use this versionTue, 30 Mar 2010 09:00:54 GMTDaveeeRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxYes, 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.Mon, 29 Mar 2010 14:00:45 GMTGilaMonsterRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxDoes Microsoft have any plans for discontinuing older compatibility levels? If so, where can this information be found, in particular, dates.Mon, 29 Mar 2010 09:36:45 GMTTom John-342103RE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxthanks......Thu, 02 Jul 2009 07:37:54 GMTcharipgRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspx[quote][b]DNA (6/30/2009)[/b][hr]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.[/quote]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!!Wed, 01 Jul 2009 02:29:55 GMTIan ScarlettRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxthanks..........Wed, 01 Jul 2009 00:03:19 GMTcharipgRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspx[quote]what is cmpt level?why should we change cmpt level if we move database from sql2000 to sql2005 ?[/quote]cmpt is the database compatibility level. 70 for sql server 7.080 for sql server 200090 for sql server 2005100 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.thanksTue, 30 Jun 2009 02:02:21 GMTklnsudduRE: compatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxAs 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.Tue, 30 Jun 2009 01:58:45 GMTDNA_DBAcompatability levelhttp://www.sqlservercentral.com/Forums/Topic744192-146-1.aspxHi,what is cmpt level?why should we change cmpt level if we move database from sql2000 to sql2005 ?thanks in advance.....Tue, 30 Jun 2009 00:21:33 GMTcharipg