Understanding SQL Server Compatibility Levels

Last Updated on August 13, 2020 by John Morehouse

I recently had a colleague, Robert,  stop by my desk and with a question about the compatibility level of a database.  Essentially, he was interested in upgrading a database’s compatibility level but at the same time have the ability to down grade it if issues were found. Robert had a concern that if we had to downgrade, this action would impact the database internal structure or version.

I know that this is an old topic since I found a post from by Jonathan Kehayias (B|T) that talks about the exact same thing, but I think that it’s worth repeating. Paul Randal (B|T) also has one here.

What is the Database Compatibility Level?

The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version.  This functionality isn’t all encompassing as only certain aspects (i.e. certain syntax) of the database would pertain to this setting.  For more details as to what is or isn’t affected, check out Books Online.

We know from various online sources that these are the following compatibility levels:

SQL Server 6.0 – 60
SQL Server 6.5 – 65
SQL Server 7.0 – 70
SQL Server 2000 – 80
SQL Server 2005 – 90
SQL Server 2008 – 100
SQL Server 2012 – 110
SQL Server 2014 – 120
SQL Server 2016 – 130
SQL Server 2017 – 140
SQL Server 2019 – 150

Starting with SQL Server 2016, additional versions are starting to be supported.  SQL Server 2016 supported 4 versions (including itself) and SQL Server 2017 will support 5.  It also appears that SQL 2019 will support 6, back to compatibility level 100.  Previous versions, 2014 and newer, only supported three database compatibility levels.

Here is a listing of the available compatibility levels:

Compatibility Levels
Version Current Compatibility Level Oldest Available Level
SQL 2019 150 100
SQL 2017 140 100
SQL 2016 130 100
SQL 2014 120 100
SQL 2012 110 90
SQL 2008 100 80
SQL 2005 90 80
SQL 2000 80 80
*NOTE: I do not list anything prior to SQL Server 2000 as I don’t have a way to validate what (if any) compatibility levels are available.  Also note that SQL Server 2008 R2 while a newer version still maintains the same compatibility levels as SQL Server 2008.

You can see what compatibility level a database is at by using the UI or via code.

Via UI:

  1. Right click the database
  2. Select Properties.
  3. Go to the Options tab

The following dialog window will show the compatibility for that particular database.  In the example below, you can see that the compatibility mode for the database DBVault is currently 2012 or 110.

compatibility_1

 

 

 

 

Via Code:

For SQL 2005 & newer:

SELECT name, compatibility_level from sys.databases WHERE name = 'DatabaseNameHere'

For SQL 2000:

SELECT name, cmptlevel from sysdatabases WHERE name = 'DatabaseNameHere'

If you want to see the compatibility for all databases of the instance, just leave off the WHERE clause.

What is the Database Version?

The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of.  If you migrate a database from an older version to a newer version, the database version value will be increased to reflect the version number of the new server’s model database.  The database version does not equal the compatibility level and should be considered as a completely different attribute of the database.

When you create a database, the database version is “stamped” with the same version as the Model database.  It’s worth noting that if the Model database was originally created on a different server edition and then subsequently upgraded, you potentially could end up with slightly different numbers than what you might expect.  As you upgrade the database to new SQL Server edition (you can’t go backward) the version of the database increases. This is done automatically regardless of what method you use to upgrade the database to the new version of SQL Server.

Here are some database versions numbers for each version of SQL Server:

Database Version Levels
SQL Server Version Database Version
SQL 2019 895
SQL 2017 868
SQL 2016 841
SQL 2014 782
SQL 2012 706
SQL 2008 R2 661
SQL 2008 655
SQL 2005 611
SQL 2000 539

I did find this article that has a more complete listing of database version values.

It is worth noting that service pack releases will most likely increase the database version of all the attached databases.  There are a couple of ways to determine what database version a database is currently at. There is not, however, any way to do this through a UI that I’m aware of.  Here are 3 ways in code that I know how to get this information.

-- using DBCC PAGE to look at the boot page (9) of the database
DBCC TRACEON (3604)
DBCC PAGE('DatabaseName',1,9,3)
DBCC TRACEOFF (3604)
GO
--using DBCC DBINFO
DBCC TRACEON (3604)
DBCC DBINFO
DBCC TRACEOFF (3604)
GO

-- useing database property
SELECT DatabaseProperty('DatabaseNameHere','version')
GO

You’ll note that for each DBCC command we have to turn on trace flag 3604 so that the output of the DBCC command is sent to the SSMS window rather than the default location, the SQL Server log.

Ironically, if you are still on SQL 2000, you can see this information with a simple query:

-- useing database property
SELECT name, version from master.dbo.sysdatabases
GO

If you use the DBINFO/PAGE option, you will need to look for the hi-lighted values in the output:

dbVerionExample

 

In the above example, you can see that I’m looking at the AdventureWorks database, the database version is currently set at ‘706’ and it was created with version ‘705’.  This tells me that the database was initially created on a SQL Server 2012 instance (possibly a CTP version) and then subsequently upgraded when restored to my test SQL Server 2012 SP2 instance.

Bringing It All Together

Now that we understand the difference between the compatibility level and the database version, let’s bring it all back to our initial story.   If I recall correctly, the database in question started off as a SQL Server 2000 database which is a compatibility of ’80’ and an internal version of ‘539’.   As mentioned, through the course of the years we have been able to upgrade it to SQL Server 2008 and currently in the process of getting it moved to SQL Server 2012.

Since we know that the database was migrated to SQL Server 2008, the database version was increased to match the model database of that instance, so it was “stamped” with ‘655’.  The problem is that while the database version was increased, the compatibility mode was never upgraded (it slipped through the cracks as a migration step) to reflect SQL Server 2008 and remained at the SQL 2000 level of ’80’.

Since we also know that the database version is related to the version of SQL Server the database is sitting on, we know that we can adjust the compatibility level accordingly without affecting the database version.   Thankfully we have a good testing environment and were able to easily adjust the compatibility level to reflect SQL 2008 level of 100 and verify that nothing was impacted.  Once testing was complete, we made the change in production knowing that we were not going to affect any subsequent applications.

Did I mention that we tested?  Even with this change, you will want to test your applications to ensure that all of your code continues to work as you expected.

As a side note, we now have steps and/or scripts in place, so that when a database migration occurs upgrading the compatibility level to a higher level is accomplished.

Enjoy!

PS – Glenn Berry has an excellent post on the differences of the newer compatibility levels.  I suggest you read his post as well regarding compatibility levels.  

© 2014 – 2020, John Morehouse. All rights reserved.

21 Replies to “Understanding SQL Server Compatibility Levels”

  1. Should I encounter any rimple in upgrading a database from database version 661 with compatibility level 100 to a database with database version 706 with a compatibility level 100.
    Some of my suppliers a very reluctant to respond with a certification and just respond : Give it a go !

  2. Hi Jan! Of course they say that. 😉 Without knowing your database I can’t really tell you one way or another to be honest. I would not think you’d see anything but it really depends on the code. I’d strongly advise that you test everything appropriately before doing the upgrade in a production environment. If you have intimate knowledge on the database, I would check the Books Online link in the post to see if you have anything that might be affected. Definitely test first though!

  3. Hi John. The Books online link gives a description of the differences encountered within SQL Server 2012 with different compatibility levels. I am looking for a list of differences between a database with database version 661 and compatibility level 100 as opposed to a database with database version 706 and compatibility level 100.

  4. Hey Jan –

    I don’t know of any list that would list the internal differences between 661 & 706 as you described. If you find any such list I’d be interested in seeing it! Thanks!

  5. Hi,

    I just want to migrate SQL Server 2008 R2 to New version of SQL Server (2012 or 2014) with HA and DR. Could you please suggest me which is prefer..

    Thanks,
    Rathan.

  6. Hi Rathan –

    My recommendation would be to jump to 2014 as it’s new and it offers up additional features, such as additional readable secondaries in the AG group. Plus, given that it’s newer it’ll be in mainstream support from Microsoft for several years. It all depends on what your business requirements are for your HA/DR needs.

  7. After restoring from sql server 2005 to 2008 r2 and changing the compatibility level our application still does not connect. Can you please help

  8. Hi Sangarika- the compatibility level shouldn’t prevent any application from connecting to the database. It will only affect how certain queries are ran. I would suggest looking into the SQL server log to see if there are any specific error messages. That’s where I would start. Hope that helps!!

  9. thnx for ur reply.. yes sorry i was nit clear last time.. application can connect but few queries does not run and our application log shows that..

  10. Hi John can you make change in your code to show that sql 2016 is having compatibility for sql 2008(100)

  11. is the compatibility level change after doing a restore from high to low version

  12. Hi Pavan – you can’t restore from a higher version of SQL Server down to a lower version. The engine won’t allow that to happen. Or were you referring to something else? When you restore the database, the compatibility level won’t change.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.