Sql server Upgrade vs Compatibility Level

Question

Hi,
I have upgraded my sql server instance to a new version.
Someone tolled me that if we do not change the compatibility to the latest version, It is like we haven't done anything.
Is it true????

Answer 1

@Arash Masroor

Please go thru the below list of COMPATIBILITY_LEVEL in SQL server.

Sets Transact-SQL and query processing behaviors to be compatible with the specified version of the SQL engine. For other ALTER DATABASE options, see ALTER DATABASE.

Syntax
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

----------

Please don’t forget to "Accept the answer" and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi @Arash Masroor ,
Which version of SQL Server are you upgrading to?

Generally speaking, whether to change the compatibility level is not the main issue, but unless the compatibility level of the database is changed, some functions will not be available. When migrating from an older version of SQL Server to SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression.

The classic scenario for why you might want to use an older database compatibility level after an upgrade to a newer version of SQL Server is to use it as an interim migration aid to work around some application compatibility issues that might surface after a migration (which you hopefully discovered during your pre-migration testing efforts). After further testing and possible database/application changes, it is pretty common to eventually change the database compatibility level to match the database engine version of the instance, which lets you take advantage of some of the new features and behavior differences in that version.
If you upgrade SQL Server to 2016 and higher, you can use Query Store to find and correct query plan regressions.

And SQL Server 2017 adds automatic plan correction which leverages Query Store to try to force the last known good plan for query plans that have performance regressions.

Please refer to Upgrading SQL Server–Diagnosing Performance Regressions and ALTER DATABASE (Transact-SQL) Compatibility Level for more details.

In addition, please avoid posting the same question repeatedly, thanks for your understanding and support.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

What do you mean exactly by "It is like we haven't done anything". Ask this "someone" that question. It is a very vague statement and need qualification.

Of course you are running a newer version of SQL Server! For instance, the older version might not be supported. There are improvements in general in a newer version.

As for performance, specifically, SQL Server will try to give you the same execution plan as on the older version when you run that compatibility level. That is so you don't run into surprises. There are ways to handle this, as outlined by Amelia.

Answer 4

It is like we haven't done anything.
Is it true????

No it is not. With an upgrade you will get the benefit of several new features, exactly depends on which version you upgraded to. But if you stay on a lower compatibility there are some improvements that will be available to you. Typically, those are feature that somehow could be beaking your application. For instance, a statement that requires a new reserved keyword would not be accessible. (I don't think there has been any such example since MERGE was added in SQL 2008.) Maybe the most apparent example is with query plans. If you stay on the lower compat level, you are more or less guaranteed to get the same query plans as before, and thus you cannot be a victim to performance regressions. But it also means that you will not get benefit of improvements in the optimizer.

Answer 5

As others have said, that is not true.

The compatibility level only affects very specific features when running queries. The engine and all other features are upgraded to the new version.

Please see:

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#differences-between-compatibility-levels