Deborah Melkin finds the optimizer in a “Dunno, here’s a guess” scenario:
A little while back, my coworker was having trouble with a query and asked me about a strange thing they were seeing in the execution plan: The estimated rows for the query was showing 100.
100 felt like an awfully specific number. And there were two scenarios I knew of where the SQL Server cardinality estimator immediately used that value – table variables and multi-statement table value functions with SQL 2016 compatibility or earlier or as part of an APPLY where interleaved execution doesn’t apply instead of a JOIN.
The statement in question didn’t use either so what was the issue?
Read on for the solution.