What does "compatibility level" mean in SQL Server?

Question

Reading official docs about STRING_SPLIT function, at the begining of the doc there is a note:

Note

The STRING_SPLIT function is available only under compatibility level 130 and above. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database. Note that compatibility level 120 might be default even in new Azure SQL Database.

So, what does compatibility level mean?

Answer 1

Compatibility Level refers to the way SQL Server operates in relation to a specific version of SQL Server.

Let's say for example, you have an application running well on SQL Server 2012, but you need to upgrade to SQL Server 2019 because support is ending soon. Everything runs great on SQL Server 2012 and the application has not been updated in many years. So, it's unclear how it might perform running on SQL Server 2019, or if it'll even work at all.

To ease the transition when you migrate to SQL Server 2019, you could consider leaving the database in SQL Server 2012 compatibility level, to avoid any unforeseen performance issues that may be introduced by the way the SQL engine and optimizer work in SQL Server 2019. Essentially, to the application, it's as if you're still running SQL Server 2012.

Now, the downside is that you can also not take advantage of the newer features that have been introduced since SQL Server 2012, such as STRING_SPLIT.