Bad habits : Clinging to old compatibility levels

[See an index of all bad habits / best practices posts]

I often see people struggling with inexplicable syntax issues, or behavior that works differently on two different servers running the same version of SQL Server (or even two different databases on the same instance). Since error messages aren’t generally written to take new versions in old compatibility levels into consideration, and since behavior differences without error messages are hard to notice (never mind debug), it’s very hard to classify and catalog every single thing that could go wrong because the compatibility level is not current. But a lot of things can do exactly this.

The documentation lists a slew of things that can change between compatibility levels, sometimes producing error messages that didn’t exist before, but sometimes changing behavior in much more subtle ways:

• sp_dbcmptlevel (Transact-SQL)
• ALTER DATABASE Compatibility Level (Transact-SQL)

I also tried to put together a thorough list of the highest impact and/or most common scenarios in this dba.stackexchange answer:

• What is the actual behavior of compatibility level 80?

That is not, by any means, an exhaustive list; just some of the more common examples.

I didn’t want to try to repeat all of those specific issues here, but rather talk about this habit in a more general sense. There are many reasons why you might still be in an older compatibility mode, and please don’t take any of these as derogatory:

• Known or perceived risk in the change – you might have old code that will break (or you’re not sure), or even the necessary recompilation of all stored procedures as a result of the change introduces a potential performance impact.
• Lack of budgeted time or effort to test the change – if it ain’t broke, don’t fix it, right? (Until you’re not able to use some functionality because you’re still in an old compatibility level.)
• Ignorance – not the bad kind, you just might not even know that some database is in an older compatibility level. You don’t know what you don’t know, right? That’s why this post is here. 🙂
• Vendor enforcement – which, quite frankly, is often due to one or more of the above reasons on their side.

Between all of these resources, you should be able to determine what kind of things you’ll need to watch out for (and please note that not all of these things will ever be caught by the SQL Server Best Practices Analyzer or the SQL Server Upgrade Advisor – not that those are bad ideas either, if you are planning an upgrade). Identifying everything that might break is going to be an extremely tedious ordeal, even if all of your T-SQL code is static and inside the database. There are further complications from dynamic SQL, encrypted SQL, ad hoc SQL in the application, ORMs, etc.

A much easier way to ensure moving forward will not break your application(s) would be to take a backup of your database, restore it as a different name, change the compatibility level, and run your unit tests against it (or just unleash your application on it). Be sure to test a full business cycle, because you might have breaking changes in that report that your CFO runs once a quarter.

But why should I bother?

So why is it a problem having the odd database in an older compatibility level? Well, one issue is consistency, especially for the same database in your dev, test, staging, QA, and production environments. The other, far more important issue, is that as long as your database is in an old compatibility level, you could continue writing code that only works, or only works the way you observe, because of the compatibility level. This is just piling up what many people might call technical debt – you might be able to skip some work now, but you’ll pay for it later. And you might keep making this database harder and harder to upgrade, which will really hurt when there is some new feature that is blocked because of the compatibility level.

See, there’s one other important point to remember: you can’t keep old compatibility levels forever. Today if you backup a SQL Server 2005 database (or a database on SQL Server 2012 in 90 compatibility level), and restore it on SQL Server 2014, it will be silently upgraded from 90 to 100 – if this is what you are going to do in production, better test it somewhere else first, because it’s something you will need to deal with. If you never plan to move your database to a more modern version of SQL Server, there’s *less* to worry about, but it is not zero. You could still be suffering (or learning from!) one of the dozens of things that behave a certain way solely because of the compatibility level, and might be surprised, frustrated or worse to find that it doesn’t work the same way elsewhere.

Even if you don’t plan to do anything about it in the short term, at least take an inventory to see where you might run into this issue in the future:

DECLARE @v TINYINT;
 
SET @v = PARSENAME(CONVERT(SYSNAME,SERVERPROPERTY(N'ProductVersion')),4) + '0';
 
SELECT name, [compatibility_level]
  FROM sys.databases
  WHERE [compatibility_level] < @v
  ORDER BY [compatibility_level];
 
-- note: this will break when we hit compat level 260 (but so will sys.databases)

[See an index of all bad habits / best practices posts]

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.