Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?
I'm using a copy of the StackOverflow2013 database. SQL Server 2019 (Compatability Mode 100)
To demonstrate the problem, I've created a simple function, which checks if User.DisplayName has an 's' in it:
CREATE FUNCTION dbo.IsUserS (@DisplayName nvarchar(80)) RETURNS bit AS BEGIN RETURN CHARINDEX('S', @DisplayName) END
Here's the query that uses the function:
SELECT U.ID, U.[DisplayName], U.[Reputation] FROM [dbo].[Users] AS [U] WHERE [U].[CreationDate] > '20100101' AND [U].[Reputation] > 100 AND dbo.[IsUserS](u.[DisplayName]) = 1
I'm comparing the plan for this query to the exact same query, but without the function call. Here are the plans:
In both plans, we start with a clustered index scan. For the bottom plan (the one without the filter), the estimate vs actual values for the Scan operator are close. For the plan with the filter, the estimate is way off (36% of the actual number rows).
My question is, why does the presence of the UDF alter the estimate for the preceding index scan?
Note that I'm running in Compat' mode 100. If I flip to 150, the estimate vs actual is actually worse, unless I turn on the legacy cardinality estimator on (in which case, it is the same as 100, so still bad).