After looking at it for a minute, I realized that I had never actually checked to see if a recompile hint would allow the optimizer more freedom when dealing with them.
CREATE INDEX u ON dbo.Users(DisplayName); DECLARE @d nvarchar(MAX) = N'Jon Skeet'; SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u WHERE u.DisplayName = @d; SELECT COUNT_BIG(*) AS records FROM dbo.Users AS u WHERE u.DisplayName = @d OPTION(RECOMPILE);
Turns out that it won’t, which is surprising.
Even though both plans have sort of a weird seek, the filter operator remains as a weird sort of residual predicate.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.