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 11 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);

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

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.