Microsoft showed quite interesting demo (https://www.youtube.com/watch?v=oEpJB87Xg9U), where they generate 5 million rows for an empty table
As I can see there are no loops or references to other tables with 5 million rows. I would expect 10 rows here. What am I missing here?
The FROM clause is using the old-style join syntax with the a CTE specified 9 times. This is effectively a CROSS JOIN cartesian product of the 10-row CTE so that 10*10*10*10*10*10*10*10*10 rows are generated but only 5000000 are returned due to the TOP clause.
See this answer for an example of this technique using the newer CROSS JOIN syntax, which generated 2 billion rows in about 6 minutes on my box.
