Given two cascading, self-contained (no real tables) recursive CTE's:
create view NumberSequence_0_100_View as with NumberSequence as ( select 0 as Number union all select Number + 1 from NumberSequence where Number < 100 ) select Number from NumberSequence; go create view NumberSequence_0_10000_View as select top 10001 v100.Number * 100 + v1.Number as Number from Common.NumberSequence_0_100_View v100 cross join Common.NumberSequence_0_100_View v1 where v1.Number < 100 and v100.Number * 100 + v1.Number <= 10000 -- please resist complaining about "order by in view" for this question order by v100.Number * 100 + v1.Number go
Then generate estimate/actual plans for:
select * from NumberSequence_0_10000_View
Runtime 23ms but estimating just one row for final output (2 rows for just the first view).
The problem is that when this is used as a subquery to join with real data (by "DaysAgo" for instance), the plan is usually a very slow nested loop and I often need to add a join hint/reverse order etc.
Is there anyway to improve the estimate while keeping the CTE approach? Has there ever been a request for a "with (AssumeMinRows=N)" hint? That seems like a great general purpose helper for many cases (not just CTEs).