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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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.
There are many questions on SO about errors because of lower levels in newer versions for you to review and hopefully get an understanding
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)