Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Add variable number of rows into a table based on the values in another table (without cursors/while loops) Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 10:26 PM
Points: 9, Visits: 151
The article is great. had a question: In Production, is it possible that the login that runs such a query might not have access to Master..syscolumns and so I should use some table from within my own database that i know is always 10k in size or more? Maybe a stupid because it is possible that everyone has read access to such system views. I will test but thought someone may have an answer. BOL says: "In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission."
Post #1434060
Posted Thursday, March 21, 2013 3:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 22,504, Visits: 30,210
You could build a tally table in your database an use it directly, or you could use the following as a start for building a dynamic tally table when needed with a query:


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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434061
Posted Thursday, March 21, 2013 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 10:26 PM
Points: 9, Visits: 151
wow. super coding! Thanks!
Post #1434063
Posted Friday, March 22, 2013 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 35, Visits: 568
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
Post #1434270
Posted Friday, March 22, 2013 7:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 22,504, Visits: 30,210
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434276
Posted Friday, March 22, 2013 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1434371
Posted Saturday, March 23, 2013 7:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
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


I'll throw in with the others on that. That's what is know as a "Counting rCTE". Another name for it is "Hidden RBAR". Please see the following article for a comparison of rCTEs to 3 other common methods. The rCTE loses on all fronts.
http://www.sqlservercentral.com/articles/T-SQL/74118/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1434648
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse