One of the most common questions I receive after my free webinar is about Database Compatibility Level in SQL Server. In this article, I will explain what Compatibility Level is and how you can change it to different versions of SQL Server.

Compatibility Level refers to the version of SQL Server that your database is compatible with. By changing the Compatibility Level, you can take advantage of the features and improvements introduced in newer versions of SQL Server. However, it is important to understand the risks involved and thoroughly test your system before making any changes.

Here is an example script that demonstrates how to change the Compatibility Level of a database:

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 a few instances when you may need to change the Compatibility Level of your SQL Server database. One scenario is when you restore an older database from an earlier version of SQL Server to the latest version. Another scenario is when you have explicitly changed the Compatibility Level to an earlier value.

It is recommended to set your database to the Compatibility Level of the version of SQL Server you are running to get the maximum performance and functionality. However, it is important to note that if you are upgrading from an earlier version of SQL Server, you should thoroughly test your application for any errors. Changing the Compatibility Level may cause issues if your application is using older code that is no longer compatible with the latest version.

Here are a few important points to remember:

• There is no need to restart SQL Server after changing the Compatibility Level.
• The new Compatibility Level is applicable immediately for all future queries.
• You can revert back to an earlier Compatibility Level in case of any errors.
• You can move forward or fall back on the Compatibility Level at any point in time.

It is crucial to thoroughly test your system after changing the Compatibility Level to ensure that there are no errors. The best practice for changing the Compatibility Level of a database is to follow these three steps:

1. Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER.
2. Change the Compatibility Level of the database using the appropriate ALTER DATABASE statement.
3. Put the database back in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

By understanding and managing the Compatibility Level of your SQL Server databases, you can ensure optimal performance and compatibility with your applications. Remember to always test your system thoroughly before making any changes.