Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected.
As far as I understand compatibility modes, it is about the availability and support of certain language structures between the various versions of SQL Server.
It does not affect the inner workings of the database engine version. It would try to prevent use of features and constructs that were not yet available in earlier versions.
I just created a new database with compat level 80 in SQL Server 2008 R2. Created a table with a single int column and populated it with a few rows.
Then executed a select statement with a row_number()
function.
My thought was, since the row_number function was only introduced in 2005, this would throw an error in compat 80 mode.
But to my surprise this worked fine. Then, surely, the compat rules are only evaluated once you 'save something'. So I created a stored proc for my row_number statement.
The stored proc creation went fine and I can perfectly execute it and obtain results.
Could someone help me to better understand the working of compatibility mode? My understanding is obviously flawed.
Best Answer
From the docs:
In my interpretation, compatibility mode is about behavior and parsing of syntax, not for things like the parser saying, "Hey, you can't use
ROW_NUMBER()
!" Sometimes the lower compatibility level allows you to continue getting away with syntax no longer supported, and sometimes it prevents you from using new syntax constructs. The documentation lists several explicit examples, but here are a few demonstrations:Passing built-in functions as function arguments
This code works in compatibility level 90+:
But in 80 it yields:
The specific problem here is that in 80 you aren't allowed to pass a built-in function into a function. If you want to stay in 80 compatibility mode, you can work around this by saying:
Passing a table type to a table-valued function
Similar to the above, you can get a syntax error when using a TVP and trying to pass it to a table-valued function. This works in modern compat levels:
However, change the compatibility level to 80, and run the last three lines again; you get this error message:
Not really any good workaround off the top of my head, other than upgrading the compat level or getting the results a different way.
Using qualified column names in APPLY
In 90 compatibility mode and up, you can do this without problem:
However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:
ORDER BY an alias that happens to match a column name
Consider this query:
In 80 compatibility mode, the results are as follows:
In 90 compatibility mode, the results are quite different:
The reason? In 80 compatibility mode, the table prefix is ignored entirely, so it is ordering by the expression defined by the alias in the
SELECT
list. In newer compatibility levels, the table prefix is considered, so SQL Server will actually use that column in the table (if it is found). If theORDER BY
alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:The result is ordered by the
myname
expression in 80, because again the table prefix is ignored, but in 90 it generates this error message:This is all explained as well in the documentation:
ORDER BY something not in the SELECT list
In 90 compatibility mode you can't do this:
Result:
In 80, though, you can still use this syntax.
Old, icky outer joins
80 mode also allows you to use the old, deprecated outer join syntax (
*=/=*
):In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:
In SQL Server 2012, this is no longer valid syntax at all, and yields the following:
Of course in SQL Server 2012 you can no longer work around this problem using compatibility level, since 80 is no longer supported. If you upgrade a database in 80 compat mode (by in-place upgrade, detach/attach, backup/restore, log shipping, mirroring, etc.) it will automatically be upgraded to 90 for you.
Table hints without WITH
In 80 compat mode, you can use the following and the table hint will be observed:
In 90+, that
NOLOCK
is no longer a table hint, it's an alias. Otherwise, this would work:But it doesn't:
Now, to prove that the behavior is not observed in the first example when in 90 compat mode, use AdventureWorks (making sure it's in a higher compat level) and run the following:
This one is particularly problematic because the behavior changes without an error message or even an error. And it's also something that the upgrade advisor and other tools might not even spot, since for all it knows, that's a table alias.
Conversions involving new date/time types
The new date/time types introduced in SQL Server 2008 (e.g.
date
anddatetime2
) support a much larger range than the originaldatetime
andsmalldatetime
). Explicit conversions of values outside the supported range will fail no matter what the compatibility level, for example:Yields:
However, implicit conversions will work themselves out in the newer compatibility levels. For example this will work in 100+:
But in 80 (and also in 90), it yields a similar error as above:
Redundant FOR clauses in triggers
This is an obscure scenario that came up here. In 80 compatibility mode, this will succeed:
In 90 compatibility and higher, this no longer parses, and instead you get the following error message:
PIVOT/UNPIVOT
Some forms of syntax won't work under 80 (but work just fine in 90+):
This yields:
For some workarounds, including
CROSS APPLY
, see these answers.New built-in functions
Try using new functions like
TRY_CONVERT()
in a database with compatibility level < 110. They are simply not recognized there at all.Result:
Recommendation
Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.