Using SQL Server 2019 but database compatibility is 2008

Ali Al Hajri 1 Reputation point
2022-10-30T11:06:39.803+00:00

to whom is may concern,

I would like to know the impact of using Using SQL Server 2019 but database compatibility is 2008

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2022-10-30T12:37:14.17+00:00

    This means that some features will not be available to you. A few things comes to mind:

    -- The string_split function.
    -- OPENJSON.
    -- You cannot use the WITHIN GROUP clause with the string_agg function.

    On the other hand, switching from compatibility level 100 to 150 may mean that there are things that break. In terms of functionality, it is difficult to give examples, because they are quite subtle. But they do exist, and you could run into them. What may be more visible is that some queries start to execute slower. Or for that matter faster. But typically, people only complain when performance degrades. This is due that there have been changes in the optimizer. Often to the better, but ever so often there is a query the change backfires for one reason or another.

    Generally, I recommend when you to a new version of SQL Server that you also move the compatibility level to get access to all features and improvements. And thanks to the introduction of Query Store, there are good methods to capture the queries that degrades. For instance, enable Query Store and run with the old compat level for a while. Then switch, and Query Store can help you find the degrading queries, and you can also force the old plan as a temporary measure.

    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-10-31T02:27:41.503+00:00

    Hi @Ali Al Hajri ,

    If the database compatibility is 2008(100), then this database cannot use enhancements that are only available in a higher database compatibility level (150). For the details about differences between compatibility levels, please read this MS document.

    Microsoft’s recommended upgrade process is to upgrade to the latest SQL Server version, but keep the source database compatibility level. Then, enable Query Store on each database and collect baseline data on the workload. Next, you set the database compatibility level to the latest version, and then use Query Store to fix your performance regressions by forcing the last known good plan. Refer to MS document Change the Database Compatibility Level and use the Query Store to get more detail.

    255473-capture.png

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments