SQL server Compatibility option


  • My impression was that compatibility option allows you to use current version features without breaking any older version features including syntax or deprecated features. In my case, I restored from 2005 to 2008, so all dbs have compatibility level for 2005 to avoid any issues for all codes written in 2005. Now I'm getting compatibility error when trying to use CUBE and ROLLUP and must to set the compatibility level to 2005. Dahh!! I'm in 2008  and must use 2008 compatibility??? I thought it should take care of backward compatibility. Shoulddn't it?



    Wednesday, September 18, 2013 5:37 PM


All replies

  • Even though SQL Server 2008 is installed, if the compatibility is set to 2005 version then only the 2005(or lower) version features are supported.

    CUBE and ROLLUP are introduced in SQL 2008 version

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 18, 2013 5:48 PM
  • @Serat GROUP BY with CUBE and ROLLUP are _not_ new in SQL 2008. See GROUP BY  Only Grouping Sets is new in SQL 2008.

    Can you post an example of a query that requires 9.0 compatibility mode because of GROUP BY and CUBE?



    Wednesday, September 18, 2013 5:56 PM
  • Hi

    Compatibility mode is there to help people migrate applications that have functions that are no longer compatible with newer versions of SQL. If you have applications that require functions no longer supported in sql 2008 you would want to run them in compatibility mode 90;

    But in your case if you are trying to use a feature which available only from 2008 version , you have to use  the compatibility 100 only.

    Better approach would be to fix issues which is preventing you from using the compatibility 100 and make use of the features in 2008 version,since you have already moved your DB to the higher version.


    Wednesday, September 18, 2013 5:58 PM
  • Hello,

    Please refer to the following article related to Differences Between Compatibility Level 90 and Level 100:
    For CUBE and ROLLUP, if Compatibility-level setting is 90, CUBE and ROLLUP are not enforced as reserved keywords;Compatibility-level setting is 100, CUBE and ROLLUP are reserved keywords within the GROUP BY clause.

    Just as David post above, please post the query statement you using for further analysis.

    Fanny Liu

    If you have any feedback on our support, please click here.

    Fanny Liu
    TechNet Community Support

    Thursday, September 19, 2013 1:50 AM
  • Basically, compat level try to as much as possible freeze you at that level. The new syntax for CUBE and ROLLUP (which I assume you are using) was introduced in 2008 so it is only reasonable that this isn't available in 2005 level - those things weren't available in SQL Server 2005 so why should they be available in 2008 in 2005 mode? This is basically how you should think about compatibility levels.

    For your particular situation you have the option to use the old syntax for CUBE and ROLLUP (check out Books Online for SQL Server 2005 and you will see that syntax).

    The "right" solution is of course to amend you app so it runs in 2008 level, allowing you to use the new syntax for CUBE and ROLLUP.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, September 19, 2013 6:35 PM