with
    e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
    e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
    eTally(n) as (select row_number() over (order by (select null)) from e2 a cross join e2 b) -- 10,000 rows

WITH E1(N) AS (
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            ),                          --10E+1 or 10 rows
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c) 
   
SELECT 
	docid = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 
	Pages = (ABS(CHECKSUM(NEWID()))%4) + 1
INTO #X 
FROM E4; 

CREATE UNIQUE CLUSTERED INDEX ucx_docid ON #x (docid)
UPDATE STATISTICS #x WITH FULLSCAN



DECLARE @docid INT, @pages INT

PRINT ''
PRINT 'Q1 ================================================================================='
SET STATISTICS TIME, IO ON;

WITH E1(N) AS (
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
             SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            ),                          --10E+1 or 10 rows
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c), 
   Tally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) from E4)

SELECT 
	x.docid, 
	y.pages
INTO #Q1
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = n FROM Tally) y 
ORDER BY x.docid, y.Pages; 

SET STATISTICS TIME, IO OFF;


PRINT ''
PRINT 'Q2 ================================================================================='
SET STATISTICS TIME, IO ON;

SELECT 
	x.docid, 
	y.pages
INTO #Q2
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = ROW_NUMBER() OVER(ORDER BY docid) FROM #X) y 
ORDER BY x.docid, y.Pages; 
SET STATISTICS TIME, IO OFF;


PRINT ''
PRINT 'Q3 ================================================================================='
SET STATISTICS TIME, IO ON;

WITH CTE AS
(
	SELECT 
		docid, pages 
	FROM #x 
	UNION ALL 
	SELECT 
		C.docid, C.pages-1 
	FROM CTE C 
	INNER JOIN #x X  
		ON X.docid = C.docid
		AND C.pages-1 > 0
)

SELECT 
	docid, 
	Pages 
INTO #Q3
FROM CTE 
ORDER BY docid,Pages;

SET STATISTICS TIME, IO OFF;
PRINT ''
PRINT '================================================================================='

Q1 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1732 ms,  elapsed time = 1966 ms.

(2499713 row(s) affected)
 
Q2 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 2, logical reads 3002608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2605 ms,  elapsed time = 3546 ms.

(2499713 row(s) affected)
 
Q3 =================================================================================
Table 'Worktable'. Scan count 2, logical reads 19497919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 4501746, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 36036 ms,  elapsed time = 40989 ms.

(2499713 row(s) affected)
 
=================================================================================