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.

SQL SERVER - How to Change Database Compatibility Level? 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.

Solarwinds
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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – How to Protect Your Database from Ransomware?
Next Post
SQL SERVER – How to Find Row Count of Every Table in Database Efficiently?

Related Posts

2 Comments. Leave new

  • I have a question,how can I migrate datebase from new version to preview version(example from 2012 to 2008, and I don’t use extra function only on 2012),if can’t, this article about compatibility can be used where?

    Reply
  • Even a production environment where there are few users accessing via application, a database, need to change to ALTER DATABASE SET SINGLE_USER?

    Environment with 3 people using.

    Thank you

    Reply

Leave a Reply

Menu