Upgrading SQL Server databases and changing compatibility levels

By:   |   Updated: 2024-05-30   |   Comments (7)   |   Related: > Database Configurations


When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your database still acts as though it is running using an earlier version of SQL Server. From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibility level is changed. This tip will show you how to check the current compatibility level, how to change the compatibility level.


The first thing that you need to do is to check the compatibility level that your database is running under. As mentioned above, any database that is upgraded using the backup and restore or detach and attach method will not change the compatibility level automatically, so you will need to check each database and make the change.

Each version of SQL Server has an internal number that differs from the version name. Here is a list of the compatibility levels and versions that you will see:

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

Identifying Database Compatibility Level

To check the compatibility level of your databases you can use one of these methods:

Using SQL Server Management Studio, right click on the database, select "Properties" and look at the "Options" page for each database as the following image shows:


Another option is to use sp_helpdb so you can get the information for all databases at once:

EXEC sp_helpdb

Or select directly from the sys.databases catalog to get the information for all databases.

SELECT * FROM sys.databases 

Compatibility Level for New Databases

When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use. The compatibility level that is used is the compatibility level of your model database.

Here is a sample CREATE DATABASE command, but as you can see there is not an option to change the compatibility level.

( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)

When creating a database using SQL Server Management Studio you have the ability to change the compatibility level on the "Options" tab such as follows:


If we use the "Script" option we can see that SQL Server issues the CREATE DATABASE statement and then issues a "ALTER DATABASE" to set the database compatibility level as shown below.

( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)

Changing Compatibility Level

So once you have identified the compatibility level of your database and know what you want to change it to, you can use the ALTER DATABASE command to make the change or you can use SSMS and right click on Properties for the database and use the Options page. There is also an older way to do this using sp_dbcmptlevel, but you should use ALTER DATABASE.

The SQL command to make the changes has the following syntax:


Compatibility Differences

There are several differences on how compatibility levels impact your database operations. Sometimes there are just new features that are added to SQL Server that you won't be able to use unless you update the compatibility level and there are also changes that impact how SQL Server processes and optimizes queries. Just because you upgrade to a later version doesn't mean that you are using all of the latest features, so be sure to check your database compatibility levels after upgrading or migrating a database.


The compatibility level setting is used by SQL Server to determine how certain features should be handled. This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking. This setting can be changed forward and backwards if needed, so if you do change your compatibility level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.

In addition, there are certain features that only work if the database is set to the latest compatibility level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibility level.

Next Steps
  • Check your database compatibility level. I have seen many databases that have been migrated to later versions of SQL Server, but the compatibility level has not been changed.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-05-30

Comments For This Article

Sunday, June 2, 2013 - 6:28:27 AM - Greg Robidoux Back To Top (25236)

@Sandeep - SQL Server 7.0 would be up to compatibilty level 70

Sunday, June 2, 2013 - 5:52:44 AM - Sandeep Back To Top (25235)


If I install sql 7.0 ,upto what compatibilty level it will support ?Means 80 and 90 or 80,90,100

Monday, October 29, 2012 - 8:52:17 AM - Greg Robidoux Back To Top (20123)

@Mike del Rosario - are you sure you are connecting to the correct SQL Server instance?  For SQL 2008 R2 you should see 80, 90 and 100.  If you are connected to a SQL Server 2000 instance you would see 70 and 80.

Monday, October 29, 2012 - 5:44:17 AM - Mike del Rosario Back To Top (20120)

I installed MS SQL SERVER 2008 R2 in my server and need to create a new database which compatibility level should be set to at least 90. However, the dropdowlist in the compatibility level of the database option does not include this level but 70 and 80 only.  Is there a way that I can make this option available? I was expecting that MS SQL SERVER 2008 R2 installation would include this level already. Hope to hear from you about it.





Tuesday, February 19, 2008 - 1:05:24 AM - Malc Back To Top (304)

Actually you the problems would specifically concern the T-SQL that worked perfectly fine under 2000 but does not work when the compat is changed to 90 i.e. after upping the level the database could be broken in some way.  It's finding the issues amongst all the stored procedures, functions etc. that is the arduous part unless you have a 100% coverage automated regression test pack.  That's why we recommend rebuilding your database to see what doesn't compile at level 90...

Monday, February 18, 2008 - 5:22:23 PM - grobido Back To Top (299)

This is true there may be issues with upping the compatiblity level, but these items should be reviewed and corrected.  I have seen many implementations of SQL Server 2005 where databases were migrated, but the compatiblity levels were never changed.  I guess if someone is not using the newer features they wouldn't notice, but at some point they would probably run into a need where they would have to up the compatiblity level.

Monday, February 18, 2008 - 12:13:21 PM - Malc Back To Top (298)

There are also issues with upping the compatibility level from a previous SQL version to version 90 - you need to also check that your code still works with the stricter rules under SQL 2005.  For an explanation take a look at this:



get free sql tips
agree to terms