SQL SERVER – How to Change Database Compatibility Level?

After watching my free webinar the most popular question, which I receive is about Database Compatibility Level.

Here is the script which shows how you can change the database compatibility level to different version of SQL Server. Please note to understand what is Compatibility Level and its advantages as well as the risk involved I suggest you to watch my free webinar video.

USE [master]
GO
-- SQL Server 2017
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140
GO

USE [master]
GO
-- SQL Server 2016
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130
GO

USE [master]
GO
-- SQL Server 2014
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 120
GO

USE [master]
GO
-- SQL Server 2012
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110
GO

USE [master]
GO
-- SQL Server 2008 / 2008 R2
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 100
GO

USE [master]
GO
-- SQL Server 2005
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 90
GO

USE [master]
GO
-- SQL Server 2000
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 80
GO

There are few instants when your SQL Server is set to earlier compatibility level – a) When you restore your older database from an earlier version of SQL Server to the latest version of SQL Server. b) When you have explicitly changed this to earlier value.

Here is my recommendation, you should set your database to the comparability of the version of SQL Server you are running to get the maximum out of your database.

Please note, that if you are upgrading from the earlier version of SQL Server, you may want to thoroughly check your application for any errors. It is quite possible when you change your compatibility level from earlier version to the latest version, your application which may be using the older code, it may no longer work.

There are few more points you should remember.

• There is no restart required after changing these settings.
• This setting is applicable immediately for all the future queries as soon as you change it.
• You can revert back to earlier compatibility level in case of any error.
• You can move forward or fall back on compatibility level at any point of time.
• Remember to test your system thoroughly to make sure that after changing these settings there are no errors.

The best practice to change the compatibility level of the database is in following three steps.

• Set the database to single user access mode by using
  ALTER DATABASE SET SINGLE_USER
• Change the compatibility level of the database.
  ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140
• Put the database in multiuser access mode by using
  ALTER DATABASE SET MULTI_USER

Reference: Pinal Dave (http://blog.SQLAuthority.com)