Take the 2-minute tour ×
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.

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.

share|improve this question

2 Answers 2

up vote 22 down vote accepted

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 (<table_alias>.<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;

Result:

Msg 104, Level 16, State 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

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 (*=/=*):

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:

Msg 4147, Level 15, State 1
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

In SQL Server 2012, this is no longer valid syntax at all, and yields the following:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*='.

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:

SELECT * FROM dbo.whatever NOLOCK; 

In 90+, that NOLOCK is no longer a table hint, it's an alias. Otherwise, this would work:

SELECT * FROM dbo.whatever AS w NOLOCK;

But it doesn't:

Msg 1018, Level 15, State 1
Incorrect syntax near 'NOLOCK'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

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:

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks 
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

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.


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.

SELECT TRY_CONVERT(INT, 1);

Result:

Msg 195, Level 15, State 10
'TRY_CONVERT' is not a recognized built-in function name.


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.

share|improve this answer
1  
Clearly that is a better answer than mine! –  Max Vernon Jun 20 '13 at 4:20
    
Thank you very much for this elaborate answer, Aaron! And for fixing my numerous spelling mistakes. –  souplex Jun 20 '13 at 4:47
1  
+1 for using "icky" –  swasheck Apr 28 at 21:06
    
SQL Server 2014 compatibility notes are found here: msdn.microsoft.com/en-us/library/bb510680(v=sql.120).aspx –  Josh Gallagher Oct 21 at 7:47

Compatibility Levels are only present to allow a controlled migration from an earlier version of SQL Server. Compat Level 90 does not preclude using new features, it simply means certain aspects of the database are retained in a way that is compatible with how SQL Server 2005 worked.

See http://msdn.microsoft.com/en-us/library/bb510680.aspx for more info.

share|improve this answer

Your Answer

 
discard

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.