Database compatibility change 80 to 90

  • I have one SQL Server instance that was migrated from SQL Server 2000 to 2005. The new databases are with the compatibility level 90, but some of them are still 80.

    I don't want to change the compatibility level only to "get the latest version", because I know there are some risks, so I whant to know are the advantages of changing the compatibility level from 80 to 90? I've made some tests and the new system views an system tables exists in the database with compatibility 80, I can create schemas etc.

    So, in fact, there is really something that I can take advantage of, by changing the compatibility level?

    I don't have problems with *= JOINS nor with ORDER BY clauses.

    Thanks in Advance

    ________________
    DBA Cabuloso
    Lucas Benevides

  • for me, the biggest advantage for switching to compatibility 90 is the row_number() function; I've solved a couple of important SQL's with that function easier than if I had to use a temp table in 80 syntax; that function has been a huge help.

    The other day I had to do a row_number over Partition by equivalent for a 2000 client, and I had forgotten just how much extra work was involved.

    concatenating columns into a single field with the "FOR XML PATH(''))", is another feature that I use a lot, but that might work in 2000, I"m not sure.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • If no 90 features are needed I would keep it at 80 level -specially if talking about an already stable production environment.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • AFAIK, the "compatibility level" is a syntax-only setting. That is, it only affects the T-SQL syntax recognized and implemented.

    There are a very few other areas and tools on SQL Server that are affected by this. For instance, I believe that some of the SSMS built-in management reports will not work on level 80 (though this may have been fixed in SP1).

    But primarily it's the 2005 T-SQL syntax and features that you are giving up These are the big ones:

    * CTE's, including recursive CTE's

    * Windowed Aggregates (Row_Number(), Rank(), etc.)

    * XML Data type, XML methods, FOR XML improvements

    * TRY-CATCH error-handling

    * varchar(MAX), nvarchar(MAX) and varbinary(MAX)

    Others inlcude:

    * DDL Triggers

    * Event & query Notifications

    * Service Broker

    Additionally, I believe that you cannot take advantage of SQLCLR either (not sure about this one though).

    (EDIT: Note, the above is not correct. Most of these features are still available under compatibility level 80).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • ... and all those features are a major reason for compatibility level 9.0.

    I have to support SSE2k databases in combination with SSE2k5 databases. The new features are really great and enable better and faster development.

    Greets

    Flo

  • Friends,

    I have just tested in the following way:

    a) Created a new database in SQL Server 2005 and in the "Compatibility level" I chose 80.

    b) Created a new table in this database

    c) Created a stored procedure using BEGIN TRY ... BEGIN CATCH

    d) Fired a query using ROW_NUMBER().

    Every step worked perfectly. That's why I am curious about this compatibility level. I just think it has no practical effects in the database except from those listed in http://msdn.microsoft.com/en-us/library/bb510680.aspx, which represents none advantage in my point of view. Unless my test is compromised by the fact that I am creating the database already in SQL Server 2005.

    I am using SQL Server 2005, SP3 version 9.00.4035.

    ________________
    DBA Cabuloso
    Lucas Benevides

  • Well, you do seem to be correct about this and I will note it on my earlier post.

    Nonetheless, the article that you point to also clearly indicates that a lowered Compatibility Level is intended only as an interim migration aid, and NOT as a permanent condition.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi !

    A question about this....

    I created an empty database in Sql 2005, restored a SQL 2000 database into that one instead of detach, attach db.

    Now that database has comp. level 80.

    Can I just change the comp. level to get the benefits of sql 2005 or do I have to redo it by using attach_db ??

    With Regards

    Jonas

  • You can just change it. You should make sure that no one else is in it (put it in single user mode first) just to insure that nothing freaky happens.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply