What is the actual behavior of compatibility level 80?

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.

From the docs:


Sets certain database behaviors to be compatible with the specified version of SQL Server.

...


Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

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+:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

But in 80 it yields:


Msg 102, Level 15, State 1
Incorrect syntax near '('.

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:

DECLARE @db_id INT = DB_ID();

SELECT * 
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

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:

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS 
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

However, change the compatibility level to 80, and run the last three lines again; you get this error message:


Msg 137, Level 16, State 1, Line 19

Must declare the scalar variable "@foo".

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:

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:


Msg 102, Level 15, State 1
Incorrect syntax near '.'.

ORDER BY an alias that happens to match a column name

Consider this query:

SELECT name = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.name;

In 80 compatibility mode, the results are as follows:

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

In 90 compatibility mode, the results are quite different:

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

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 the ORDER BY alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:

SELECT myname = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.myname;

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:


Msg 207, Level 16, State 1, Line 3

Invalid column name 'myname'.

This is all explained as well in the documentation:


When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.


For example, an ORDER BY clause with a single two-part column (.) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.


SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1


When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.

ORDER BY something not in the SELECT list

In 90 compatibility mode you can't do this:

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

Re

StackExchange Database Administrators Q#44908, answer score: 68