How to Setup Compatibility in Microsoft SQL Server?

Introduction

The compatibility level of a database is important because it determines which features are available and can also affect the performance of queries and other operations in the database. For example, if a database has a compatibility level of 100 (SQL Server 2008), certain features that were introduced in later versions of SQL Server, such as memory-optimized tables or table variables with large record sizes, will not be available for use in that database.

It is important to set the compatibility level of a database to the correct level, as changing the compatibility level of a database can cause existing T-SQL scripts or applications to behave differently. It is important to thoroughly test any changes to the compatibility level before deploying them to a production environment.

Definition

Compatibility level in Microsoft SQL Server refers to the version of SQL Server with which a database is most closely compatible. When a database is created or upgraded, it is assigned a compatibility level that determines which features are available for use in that database.

For example, if a database has a compatibility level of 100 (SQL Server 2008), certain features that were introduced in later versions of SQL Server, such as memory-optimized tables or table variables with large record sizes, will not be available for use in that database.

Set the Compatibility Level of a Database

Here are some methods of how to set the compatibility level of a database in Microsoft SQL Server −

• Using Transact-SQL Command.

• Using SQL Server Management Studio.

Using Transact-SQL Command

To set the compatibility level of a database using Transact-SQL, you can use the following command −

ALTER DATABASE [database_name]
SET COMPATIBILITY_LEVEL = [compatibility_level];

Replace [database_name] with the name of the database, and replace [compatibility_level] with the desired compatibility level (e.g. 120 for SQL Server 2014).

Using SQL Server Management Studio

• Connect to the SQL Server instance you want to set the compatibility level for.

• In the Object Explorer pane, expand the Databases folder.

• Right-click the database you want to set the compatibility level for, and select Properties.

• In the Properties window, select the Options page.

• Under Compatibility level, select the desired compatibility level from the drop-down list.

• Click OK to save the changes.

Note − Changing the compatibility level of a database can cause existing T-SQL scripts or applications to behave differently. It is important to thoroughly test any changes to the compatibility level before deploying them to a production environment.

Essential points to Setup Compatibility in Microsoft SQL

Here are some important points to consider when setting the compatibility level of a database in Microsoft SQL Server −

• Compatibility levels are specific to each database, so you will need to set the compatibility level for each database individually.

• Changing the compatibility level of a database can cause existing T-SQL scripts or applications to behave differently. It is important to thoroughly test any changes to the compatibility level before deploying them to a production environment.

• The compatibility level of a database determines which features are available for use in that database. For example, if a database has a compatibility level of 100 (SQL Server 2008), certain features that were introduced in later versions of SQL Server, such as memory-optimized tables or table variables with large record sizes, will not be available for use in that database.

• The compatibility level of a database can affect the performance of queries and other operations in the database. For example, using a higher compatibility level may result in better query performance, but may also cause some queries to behave differently.

• It is generally recommended to set the compatibility level to the latest version of SQL Server, as this will allow you to take advantage of the latest features and performance improvements. However, you should carefully consider the impact on existing T-SQL scripts and applications before making any changes.

Conclusion

• It is important to set the compatibility level of a database to the correct level, as changing the compatibility level of a database can cause existing T-SQL scripts or applications to behave differently.

• There are several ways to set the compatibility level of a database, including using SQL Server Management Studio or Transact-SQL.