Lynn Pettis (3/22/2013)
Megha P (3/22/2013)
Hello ,you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)
SELECT * FROM CTE ORDER BY docid,Pages
Your recursive CTE will be slower and won't scale as well as the straight tally method.
It's a clever little rCTE so let's have a look:
Code:
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 '================================================================================='
Results:
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)
=================================================================================
The rCTE is a lot slower.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden