I recently had a colleague, Billy Bob, 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. Billy Bob 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 5 years ago 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.
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
It’s also worth noting that the compatibility level is only available for 3 previous (including the one that you are on) versions from the current version of SQL Server that you are working with. Times have changed. 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.
Here is a listing of the available compatibility levels:
|Version||Current Compatibility Level||Oldest Available Level|
*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.
**Update: Included that 2016 also supports a compatibility level of 100. Here is a good reference: https://msdn.microsoft.com/en-us/library/bb510680.aspx
***Update: Pretty cool that SQL Server 2017 now supports 5 different compatibility levels down to SQL Server 2008. Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level. SQL Server 2017 is not RTM yet, so this is subject to change.
It was pointed out that the SSMS 2014 version (Thanks RTD!) will actually show you that a compatibility level of 90 (SQL Server 2005) is an available option.
However if you attempt to set a database to 90, you will receive the following error:
This blog post informs us that if you upgrade from a database that is in the compatibility level of 90 to SQL Server 2014, it will be upgraded to level 100.
You can see what compatibility level a database is at by using the UI or via code.
- Right click the database
- Select Properties.
- 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.
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.
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:
|SQL Server Version||Database Version|
|SQL 2008 R2||661|
***Reminder: SQL Server 2017 is not RTM yet, so this is subject to change.
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:
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.
© 2014 – 2017, John Morehouse. All rights reserved.