Earlier last week SQL SERVER 2019 is Released. It has been an amazing experience to download and work with this new version. As I have been working for almost 20 years with SQL Server, I want SQL Server to be robust and stable, while performing better than the previous version of the SQL Server in the same condition. One thing impressed me about the latest version of SQL Server 2019 support to Compatibility Level from 2008 to 2019.
Compatibility Level from 2008 to 2019
Compatibility Level is a database configuration that decides what algorithm SQL Server will use to execute queries and also support various available features. For example, you are running the latest version of SQL Server but still guide your SQL Server to behave and perform like it is any of the earlier versions of the SQL Server which is allowed by your current version.
It is one of the most important settings if you want performance from the latest version of SQL Server. Unfortunately, most of the database which I see during my Comprehensive Database Performance Health Check, I see it be set to a very incorrect value.
SQL Server 2019
SQL Server 2008 and SQL Server 2008 R2 both are out of the Microsoft Extended Support. The only option one should follow if they are using SQL Server 2008/R2, is to upgrade it to the latest version of the SQL Server. SQL Server 2019 allows you to directly upgrade from SQL Server 2008/R2 and keep the database compatibility to the earlier version.
I am personally very happy that the Microsoft team has given one small chance to the organizations which are using the earlier version of SQL Server to upgrade to the latest and greatest version of the SQL Server and without breaking the compatibility and cardinality settings.
Here is the screenshot of the SQL Server 2019 version’s server.
You can see in the dropdown, that you can go up to SQL Server 2008 compatibility in the latest version of the SQL Server.
Here is the script which you can execute if you want to change your compatibility of the database to the latest version of SQL Server 2019. Please note that the following script will only work on the SQL Server 2019.
USE [master] GO ALTER DATABASE [SQLAuthority] SET COMPATIBILITY_LEVEL = 150 GO
Have you started to use the latest version of SQL Server 2019? Please leave a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)