HomeSQL ServerFrom Legacy to Modern: How to Manage Compatibility Levels in SQL Server

From Legacy to Modern: How to Manage Compatibility Levels in SQL Server

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand. Compatibility level refers to the version of SQL Server with which a database is compatible. In this blog post, we will discuss the compatibility level of a database in SQL Server and how you can view and change it.

Compatibility Level of a Database

The compatibility level of a database in SQL Server determines how certain features of the SQL Server version are supported in the database. Compatibility levels can be set to one of several values, which indicate the version of SQL Server with which the database is compatible. The following table lists the compatibility level values for SQL Server versions:

SQL Server VersionCompatibility Level
SQL Server 2019150
SQL Server 2017140
SQL Server 2016130
SQL Server 2014120
SQL Server 2012110
SQL Server 2008 R2100
SQL Server 200890
SQL Server 200580
SQL Server 200080

As shown in the table, the compatibility level can range from 80 to 150, with higher numbers indicating compatibility with newer versions of SQL Server. The compatibility level of a database can be set or changed using SQL Server Management Studio or Transact-SQL.

Viewing Compatibility Level in SQL Server Management Studio

To view the compatibility level of a database in SQL Server Management Studio, follow these steps:

  1. Open SQL Server Management Studio and connect to the SQL Server instance.
  2. Expand the Databases folder and locate the database whose compatibility level you want to view.
  3. Right-click the database and select Properties.
  4. In the Database Properties dialog box, select the Options page.
  5. Scroll down to the Compatibility level option to view the current compatibility level.

Changing Compatibility Level in SQL Server Management Studio

To change the compatibility level of a database in SQL Server Management Studio, follow these steps:

  1. Open SQL Server Management Studio and connect to the SQL Server instance.
  2. Expand the Databases folder and locate the database whose compatibility level you want to change.
  3. Right-click the database and select Properties.
  4. In the Database Properties dialog box, select the Options page.
  5. Scroll down to the Compatibility level option and select the desired compatibility level from the drop-down list.
  6. Click OK to save the changes.

Changing Compatibility Level Using Transact-SQL

To change the compatibility level of a database using Transact-SQL, use the following syntax:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = compatibility_level

In the syntax, replace database_name with the name of the database you want to change and compatibility_level with the desired compatibility level.

For example, to change the compatibility level of a database named “AdventureWorks” to SQL Server 2019 (compatibility level 150), use the following command:

ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 150

Note that changing the compatibility level of a database can have implications for its performance and functionality. Before changing the compatibility level, you should thoroughly test your applications to ensure they are compatible with the new level.

Leave a Reply

You May Also Like

In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...
In this blog post, let’s learn about the error message “49972 – Cannot add tempdb remote file to local tempdb...