What is the database property “Compatibility Level”
7July 8, 2015 by Kenneth Fisher
What is the database property Compatibility Level? I don’t get asked to write about specific subjects very often but in this case not only did I have someone ask me to write about it but had a co-worker ask me what it is. I’ve always thought this was pretty straight forward, but then again I wasn’t trying to go into any detail either. So what is it?
Well on the surface of things it’s pretty much what you would expect. It affects how SQL uses certain features. Specifically it causes them to act like a given version of SQL. This is typically done to provide some level of backwards compatibility. Obviously, since this is a database property the compatibility only affects database level features and only for that specific database. So for example, it doesn’t affect what new DMOs are available (instance level change) but does affect the the new cardinality estimator. The new CE is used if the compatibility level is set to that of SQL 2014 or higher. The old optimizer is used if the compatibility level is set to SQL 2012 or lower.
Within the database the compatibility level is numeric and follows this table:
| Compatibility Level | Version |
| 80 | 2000 |
| 90 | 2005 |
| 100 | 2008 |
| 100 | 2008 R2 |
| 110 | 2012 |
| 120 | 2014 |
| 130 | 2016 |
The command you can use to change the compatibility level is
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130
If you need details on exactly what various compatibility levels affect you can look at the BOL entry for ALTER DATABASE COMPATIBILITY_LEVEL. The tables that show the differences are only between one version and the next so if you need to move multiple levels you will have to add the tables together. Don’t forget that the compatibility level doesn’t affect every change between versions.
If you upgrade a database by either moving it to a server with a higher version or by doing an instance upgrade in place the compatibility level will remain the same as long as that particular level is supported. Unofficially I’ve found this support to be about three versions back. New databases will be created using the compatibility level of the Model database. Usually this matches the version of the instance and should only be changed if absolutely necessary.
SQL Studies 
I do see some confusion around Compatibility Level because people think it applies to storage. Some people think the setting will let them restore the database backward to an older version. Other people think things like compression won’t work if you set the level back. Still other people think keeping your database at a lower Compatibility Level will free them from any need for testing or modernization. But no, eventually you are going to have to deal with those deprecated database features (as you point out the “window” of levels is only 3 versions big).
Last summer I had a SQL 2012 upgrade project with older RAISERROR syntax implemented in SQL 2000 or earlier. This is something that could have easily been fixed but they had to get it changed under the gun because the customer was upgrading within 30 days. (The devs claimed it would take weeks, in reality it took an afternoon) Compatibility Level is a helpful thing in the first month or two you are on a new version but you really need to get things bumped up to the current level before long. That shifting window is going to catch up with you, and you won’t be able to take advantage of new the engine features if you don’t bump the level up.
Yea, I think in general the biggest confusion is that people think that it sets it exactly to the old version not just adjusts a few features. Good point about people using comparability level to avoid upgrading and then finding themselves under the gun.
[…] sorry. You are not only working on a 2008 R2 (or earlier) instance but you are working with a compatibility level 80 (SQL 2000) database. SQL Server 2008 R2 is the last version that allows a compatibility level of […]
I’m trying to set a database to the highest compatibility level that its new SQL Server instance supports. I have seen two recommended approaches to find this maximum, each with its own pros and (perhaps) cons:
1. SELECT MAX(compatibility_level) FROM sys.databases
2. Extract the major version number from SERVERPROPERTY(‘ProductVersion’) and multiply by 10.
#1 is guaranteed to produce a compatibility level that the server supports, but is it guaranteed to get the maximum? Couldn’t someone come along and set ALL the databases to a compatibility level lower than the maximum? (I’m sure you know people who would do it just to see if they could, then forget to undo it, right?)
#2 works for SQL Server 2008 through 2016 as I write this, so it seems reasonable to hope it will work in the future, but I have not found any Microsoft documentation that says this is officially how they assign compatibility levels.
Is there any method of determining the maximum compatibility level that I can always rely upon?
Personally I would be a lot more comfortable with #2. I’ve seen servers where it gets messed up but as you said, #2 currently seems to be working. I would think there would be a way to look it up directly but haven’t managed to find one yet. I thought maybe master..spt_values but didn’t have any luck.
Has DB compatibility level doing nothing with SSIS packages?
I mean if I have made eg. packages earlier on DB with level 100 and I’d like to update level to 120 – do I have to make changes to my old SSIS packages?
Nope. Packages are independent. They have their own system. That said, regardless of the SSIS version, it’s the DB compat level that will affect how the DB functions.