Home Forums SQL Server 2012 SQL Server 2012 - T-SQL What exactly gets enabled in SQL 2012 when you set compatibility mode to 110 RE: What exactly gets enabled in SQL 2012 when you set compatibility mode to 110

  • You're 100% correct---and this behavior appears to be undocumented on MSDN. Attempting to run a query with this analytic function results in:

    Msg 10762, Level 15, State 1, Line 4

    The PERCENTILE_CONT function is not allowed in the current compatibility mode. It is only allowed in 110 mode or higher.

    The following analytic functions work normally under 100 compatibility mode in SQL Server 2012:

    CUME_DIST

    FIRST_VALUE

    LEAD

    LAG

    LAST_VALUE

    PERCENT_RANK

    The following functions are unavailable below compatibility mode 110:

    PERCENTILE_CONT

    PERCENTILE_DISC

    Further research shows no documentation anywhere regarding these functions and compatibility modes. I was able to easily reproduce (and fix) by taking a test database and switching between compatibility modes.

    I'll send a note to Microsoft advising them to update their documentation regarding both of these functions.