In SQL Server 2017 & prior versions, if you wanted to get cardinality estimations that matched a prior version of SQL Server, you could set a database’s compatibility level to an earlier version.
For example, in SQL Server 2017, if you wanted execution plans whose estimates matched SQL Server 2012, you could set the compatibility level to 110 (SQL 2012), and get execution plan estimates that matched SQL Server 2012.
This is reinforced by the documentation, which states:
Changes to the Cardinality Estimator released on SQL Server and Azure SQL Database are enabled only in the default compatibility level of a new Database Engine version, but not on previous compatibility levels.
For example, when SQL Server 2016 (13.x) was released, changes to the cardinality estimation process were available only for databases using SQL Server 2016 (13.x) default compatibility level (130). Previous compatibility levels retained the cardinality estimation behavior that was available before SQL Server 2016 (13.x).
Later, when SQL Server 2017 (14.x) was released, newer changes to the cardinality estimation process were available only for databases using SQL Server 2017 (14.x) default compatibility level (140). Database Compatibility Level 130 retained the SQL Server 2016 (13.x) cardinality estimation behavior.
However, in SQL Server 2019, that doesn’t seem to be the case. If I take the Stack Overflow 2010 database, and run this query:
CREATE INDEX IX_LastAccessDate_Id ON dbo.Users(LastAccessDate, Id); GO ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; GO SELECT LastAccessDate, Id, DisplayName, Age FROM dbo.Users WHERE LastAccessDate > '2018-09-02 04:00' ORDER BY LastAccessDate;
I get an execution plan with 1,552 rows estimated coming out of the index seek operator:
But if I take the same database, same query on SQL Server 2019, it estimates a different number of rows coming out of the index seek – it says “SQL 2019” in the comment at right, but note that it’s compat level 140:
And if I set the compatibility level to 2019, I get that same estimate of 1,566 rows:
So in summary, starting with SQL Server 2019, does compatibility level no longer influence cardinality estimation the way it did in SQL Server 2014-2017? Or is this a bug?