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.
Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, Upgrades Tagged: database settings, microsoft sql server 
