Clarification on SQL Server 2022 Compatibility Level Reduced to SQL Server 2016

Question

Title: Clarification on SQL Server 2022 Compatibility Level Reduced to SQL Server 2016

Description:

Dear Team,

We recently migrated our SQL Server environment from SQL Server 2016 to SQL Server 2022. Post-migration, our operations team reduced the database compatibility level of SQL Server 2022 to SQL Server 2016 (130). The intent behind this configuration change was to maintain application compatibility while leveraging the benefits of the newer SQL Server version.

Given this change, we seek clarification and guidance on the following points:

Supported Configuration:

Is running SQL Server 2022 with a reduced compatibility level of SQL Server 2016 (130) a supported configuration by Microsoft?

Impact on Supportability:

Since the extended support for SQL Server 2016 has ended, does lowering the compatibility level to 130 impact the supportability of the SQL Server 2022 instance? Specifically, would the SQL Server 2022 instance be considered unsupported due to this configuration change?

Technical and Performance Impacts:

Are there any known limitations, performance implications, or feature restrictions associated with using SQL Server 2022 in SQL Server 2016 compatibility mode?

Are there any recommendations or best practices from Microsoft for such scenarios?

We appreciate your assistance in addressing these queries as they are critical for ensuring compliance, optimal performance, and future planning of our SQL Server environment. Please let us know if you require any additional details or logs to expedite the resolution process.

Looking forward to your expert guidance.

Answer 1

Is running SQL Server 2022 with a reduced compatibility level of SQL Server 2016 (130) a supported configuration by Microsoft?

The supportability applies to the SQL Server version, not the compat level. Thus, using SQL 2022 with compat level 130 is supported.

Are there any known limitations, performance implications, or feature restrictions associated with using SQL Server 2022 in SQL Server 2016 compatibility mode?

Yes. When it comes to features, the new function generate_series will not be available to you. There maybe others, but this is the one I recall on the top of my head. But many new features will be available to you, for instance ledger tables and the function string_agg.

When it comes to performance, you will run with the optimizer features of SQL 2016, so you will miss out on batch mode for row store, scalar function inlining and other optimizer enhancements. Thus, you will not get the full benefit of SQL 2022. Then again, if you are on Standard Edition, you would not have access to all optimizer enhancements even with compat level 160.

Answer 2

Hi @Sankalp Panigrahi,

Specifically, would the SQL Server 2022 instance be considered unsupported due to this configuration change?

It will still support for your SQL Server2022.

For Differences between compatibility levels, you may check ALTER DATABASE (Transact-SQL) compatibility level.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.