Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Setting cmptlevel to 80 on a SQL-Server 2005 inhibits the use of PIVOT and APPLY, but doesn't affect the use of Analytical Functions. Is there any rational behind this?

share|improve this question
up vote 4 down vote accepted

Some rationale I've observed, but this isn't exhaustive and is my guesswork

Compatibility Level isn't an all-or-nothing thing. It provides partial backwards compatibility while you fix issues.

A trival example with LOCK hints

   Table (NOLOCK)   -- not allowed with level 90+


   Table WITH (NOLOCK)   -- WITH is optional for level 80

Why this is now mandatory, I don't know. To do with CTEs?

For PIVOT and APPLY I suspect that a SQL statement using these is simply illegal SQL Server 2000 syntax whereas ROW_NUMBER is just an unknown function and "less" critical

An example using CROSS APPLY. For 80 this is illegal SQL Server 2000 syntax.

   SomeTable S
   dbo.someTVF (S.column)

There are many questions on SO about errors because of lower levels in newer versions for you to review and hopefully get an understanding

FYI, full list of 80-90 and 90-100 differences

share|improve this answer
FROM table (NOLOCK) is supported in 90 (2005) and 100 (2008) – sam yi Feb 12 '13 at 16:15
@samyi: kinda true, but see this Look for "WITH keyword when you are using table hints". And for SQL Server 2008: – gbn Feb 12 '13 at 16:46

MSDN ALTER DATABASE Compatibility Level explains the behavior of the various levels. (just scroll down and you will find the comparison tables for the following topics )

you can find more specific comparison tables if you just change the Version from MSDN top menu (they are linked for top 3 versions in following list)

  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
  • 100 = SQL Server 2008 and R2
  • 110 = SQL Server 2012
  • 120 = SQL Server 2014

  • Differences Between Lower Compatibility Levels and Level 120

    • sample line (The older query optimizer is used instead of new SQL Server 2014 engine which includes substantial improvements to the component that creates and optimized query plans )
  • Differences Between Lower Compatibility Levels and Level 110
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.