What is the meaning of Microsoft Compatibility Level?

Question

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?

Answer 1

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

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

and

FROM
   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.

FROM
   SomeTable S
   CROSS APPLY --or JOIN
   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

Answer 2

• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008

This MSDN article explains the behavior of the various levels.