SQL SERVER 2019 – Supports Compatibility Level from 2008 to 2019

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.

SQL SERVER 2019 - Supports Compatibility Level from 2008 to 2019 Compatibility-Level-800x182

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.

SQL SERVER 2019 - Supports Compatibility Level from 2008 to 2019 sql server compatibility

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)

, , ,
Previous Post
SQL SERVER – 5 Resources from SQLPASS 2019 – Seattle
Next Post
SQL SERVER – Recompile Stored Procedures Two Easy Ways

Related Posts

6 Comments. Leave new

  • Thanks Pinal Dave.

    Reply
  • Vijaya Nettem
    April 16, 2020 8:28 pm

    we have 2008R2 with few databases at compatibility level of 90. Can I still upgrade them directly to 2019? Or do I have to update teh compatibility level to 100 first and then do the upgrade?

    Reply
  • Raju Vishwakarma
    April 23, 2020 8:37 pm

    We have 2008R2 (RTM) with few databases at compatibility level of 90. Can I still upgrade them directly to 2019? Or do I have to update compatibility level to 100 and service pack to SP4 first and then do the upgrade?

    Please Suggest.

    Reply
  • Raju Vishwakarma
    April 23, 2020 8:53 pm

    Did you get the answer for your post
    I am also having the same scenario . Also I my DB server is running on SQL 2008R2 RTM version

    Reply
  • Vijaya Nettem
    July 23, 2020 9:42 pm

    No, I didn’t get any answer. But I was able to successfully upgrade to 2019. I updated the compatibility level on my databases to 100 , then I did sql upgrade to 2019 from 2008R2 without any issues.

    Reply
  • Francesco Mantovani
    September 30, 2020 5:57 pm

    Does features apply to previous compatibility level?

    I mean, I’m on SQL Server 2019 but my databases are still on Compatibility Level 110 (SQL Server 2012). I want to rebuild a fragmented index with the options RESUMABLE = ON and OPTIMIZE_FOR_SEQUENTIAL_KEY = ON. Will these new feature apply even if the Compatibility Level is 110?

    Reply

Leave a Reply

Menu