Compatibility level in SQL Server refers to a database property that determines the syntax and behavior of the database, allowing it to be compatible with earlier versions of SQL Server. Each version of SQL Server introduces new features, improvements, and changes to the query optimizer, which may affect how queries are executed and how results are returned.

Setting the compatibility level of a database allows you to control the version of the SQL Server database, which in turn affects how the database behaves. This is useful for maintaining backward compatibility with older applications or upgrading a SQL Server instance while preserving the behavior of existing databases.

In this article, we will explore various methods to change compatibility levels in SQL Server.

List of compatibility levels in SQL Server and features introduced in each compatibility level.

This list details SQL Server versions and their native compatibility levels and supported compatibility levels:

Microsoft periodically introduces new features and parameters that help improve the SQL Server’s performance, and deprecates old syntax and features.

Below are new features and configuration parameters introduced with new compatibility levels.

Database compatibility level 120

1. Query Store: Query Store is a performance monitoring and troubleshooting feature that allows you to capture and analyze query performance data. It helps identify and resolve performance issues by providing historical information about query execution plans. It allows you to compare performance over time and make data-driven decisions to optimize query performance.
2. Live Query Statistics: Live Query Statistics is a real-time performance monitoring feature that allows you to view the progress and details of query execution while running. It provides insights into the actual execution plan, the progress of each operation, and the number of rows processed. It allows you to identify performance bottlenecks and optimize queries in real time.
3. Query Store Query Performance Insights: Query Store Query Performance Insights is a feature that provides graphical representations of query performance data collected by Query Store. It allows you to easily identify and analyze performance issues, such as regressed or improved queries, top resource-consuming queries, and query execution statistics. It helps you to optimize query performance.
4. Accelerated Database Recovery: Accelerated Database Recovery is a feature that improves database recovery performance and reduces downtime. It introduces a new recovery process that uses a more efficient and optimized algorithm to roll back transactions during database recovery. It reduces the recovery times and improves database availability.
5. Temporal Tables: Temporal Tables, as mentioned before, is a feature that allows you to track changes to data over time. While it provides historical data tracking capabilities, it can also have performance benefits in certain scenarios. It avoids the need for triggers or custom change tracking mechanisms and enables optimized query performance for historical data retrieval.
6. Columnstore Index Enhancements: SQL Server 2016 introduced several enhancements to columnstore indexes, including support for updateable non-clustered columnstore indexes, batch mode processing for operational analytics, and improved performance for queries involving columnstore indexes. These enhancements can improve the performance of data warehousing and analytics workloads by optimizing the storage and retrieval of large datasets.

Database compatibility level 130

The following features and parameters were introduced in compatibility level 130.

1. Batch Mode Adaptive Memory Grant Feedback: The Batch Mode Adaptive Memory Grant Feedback feature that helps to optimize memory grant allocations for batch mode queries. It allows SQL Server to adjust the memory grants dynamically for batch mode queries based on actual runtime performance. It improves the overall query performance by efficiently allocating memory resources.
2. Batch Mode on Rowstore: Batch Mode on Rowstore is a feature that extends batch mode processing to rowstore indexes. It improves the performance of analytical and data warehousing workloads. Also, it enables batch mode operations, such as vectorized query processing, to be used with rowstore indexes, resulting in faster query execution for large datasets.
3. Approximate Query Processing: Approximate Query Processing is a feature that allows you to estimate query results using statistical algorithms instead of executing the complete query. It can be used to quickly estimate query results for large datasets, improving query performance for complex analytical queries with aggregations without processing the entire dataset.
4. Intelligent Query Processing: Intelligent Query Processing is a set of features that uses machine learning and query optimization techniques to improve query performance. Some features of Intelligent Query Processing include Batch Mode Adaptive Joins, Table Variable Deferred Compilation, and Memory-Optimized TempDB Metadata.
5. Scalar UDF Inlining: Scalar UDF Inlining is a feature that allows scalar user-defined functions (UDFs) to be automatically inlined within queries. It eliminates the overhead of function calls and improves query performance. This feature can significantly speed up queries that involve scalar UDFs, by avoiding the need for separate function calls and enabling better query optimization.

Database compatibility level 150

The following features and parameters were introduced in compatibility level 150.

1. Intelligent Query Processing: Intelligent Query Processing is a set of features to improve query performance by optimizing query plans and execution. It includes features such as Batch Mode Adaptive Joins, Batch Mode Memory Grant Feedback, Table Variable Deferred Compilation, and Scalar UDF Inlining. These features can help improve query performance in various scenarios, such as large-scale data warehousing and OLTP workloads.
2. Accelerated Database Recovery: Accelerated Database Recovery, introduced in SQL Server 2019, continues to be a performance-enhancing feature. It provides faster and more efficient recovery of databases after failures or crashes, reducing downtime and improving database availability.
3. Memory-Optimized TempDB Metadata: In SQL 2019, the metadata of memory-optimized TempDB objects, such as table types and table variables, are stored in memory rather than on disk, which can improve performance for workloads that heavily utilize memory-optimized TempDB objects.
4. Resumable Online Index Rebuilds: SQL Server 2019 introduced the ability to resume online index rebuild operations after interruptions, such as a database or server restart. This can help reduce downtime and improve performance for index maintenance operations on large tables.

Important Notes

1. When you create a new database, the default compatibility level of the database will be the same compatibility level of the model database. For example, if you are creating a new database in SQL Server 2017, the database’s compatibility level will be 150. You can change it as per the application requirement.
2. When we restore a database whose backup is taken in the older version to the new SQL Server version, the compatibility level of the restored database will remain unchanged. For example, suppose you are restoring a database backup whose compatibility level is 110 on SQL Server 2017. In that case, the compatibility level of the database will remain 110. There is an exception. Suppose the compatibility level of source backup is lower than the minimum compatibility level supported by SQL Server version. In that case, SQL Server will change the compatibility level to the lowest supported version. For example, suppose we restore the backup of the database created in SQL Server 2005 on SQL Server 2017. In that case, the compatibility of the restored database will be changed to 100.
3. When we upgrade the SQL Server version to a newer version, the compatibility level of the system database (msdb, model, tempdb and resource) will be updated except for the master database. The compatibility level of the master database will remain unchanged.

View compatibility of SQL Database.

We can use sys.databases dynamic management view to check the compatibility level of any SQL database. The column named compatibility_level shows the current compatibility level of the database. The query is the following:

Query Output

Alternatively, you can view the compatibility level using SQL Server management studio. Launch SQL Server Management Studio 🡪 Connect to SQL server instance. 🡪 Right-click on Database and select Properties. In Database Properties 🡪 select the Options page. You can view the database compatibility in the right pan of the database properties dialog box.

Now, let us understand how we can change the compatibility level of the SQL database.

Change compatibility level using ALTER DATABASE statement.

We can use ALTER DATABASE statement to change the compatibility level. The syntax is as follows:

In the syntax:

1. db_name: Specifies the name of the SQL database whose compatibility level you want to change.
2. compatibility_level: Specifies the compatibility level.

Suppose you want to change the compatibility level of the demodatabase. The current compatibility level of the demodatabase is 120, and we have upgraded the SQL Server software but did not change the compatibility level of the database. Now, we want to change it to 150. The command is the following

Run the following SQL query to verify the changes.

As you can see, the compatibility level has been upgraded.

Change compatibility level using SQL Server Management Studio

Launch SQL Server Management Studio 🡪 Connect to SQL server instance. 🡪 Right-click on Database and select Properties. In Database Properties 🡪 select the Options page. You can view the current database compatibility in the right pan of the database properties dialog box. In our demo, the current compatibility of EltechDB database is 120. Select 150 from the drop-down box and click OK to apply the changes.

Once the compatibility is changed, run the following SQL Query to verify whether changes have been applied.

As you can see in the above image, the compatibility of the EltechDB has been changed.

Summary

In this article, we learned about the methods of identifying and changing the compatibility level in SQL Server. Moreover, we have reviewed the features and improvements made to each compatibility level.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022