Is 100 a magic number for cardinality estimation of SELECT * FROM [Multi-Statement-Table-Valued-Function] queries?

Question

I'm running on SQL Server 2019. Whenever I write a query of the form

SELECT * 
FROM [Multi-Statement-Table-Valued-Function]

the execution plan shows that it expected to get 100 rows from this process. Is this a consistent and documented pattern? Or is it just a quirk of my server?

I have a set of notes on cardinality estimation from SQL Server 2014 and I'm confident that the estimation methods haven't changed since 2014, but they make no mention of this magic number.

Answer 1

MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions

Source

Later versions (compatibility level >= SQL Server 2017) have interleaved execution which is also discussed in that source.

Testing in 2022 the 100 estimate is still used as an initial estimate when getting an "estimated" plan - but the interleaved execution means that this plan will be replaced.

One other relevant passage from the above article is

How does interleaved execution work for consecutive executions?

Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.