July 8, 2013 at 11:51 pm
Comments posted to this topic are about the item Using a Recursive CTE to Generate a List
July 9, 2013 at 2:17 am
Nice article, but was wondering if there was any reason you'd do that over using FOR XML PATH to concatenate a string? I'd imagine that the IO of the recursive CTE must be pretty large, whereas the FOR XML method is likely to be considerably smaller.
July 9, 2013 at 2:57 am
Small thing, but I like the first graphic. The title is not cryptic, but you don't know exactly what will follow. 2 seconds after looking at the image, you know. Nice touch.
July 9, 2013 at 3:26 am
A csv list can also be generated using "FOR XML"
I rewrote the final query using this method:
SELECT OfficeID,
(SELECT TOP(1)
C.StateAbbr
FROM dbo.OfficeCounty OC
INNER JOIN dbo.County C
ON C.CountyId = OC.CountyID
WHERE OC.OfficeID = O.OfficeID) AS StateAbbr,
STUFF((SELECT ',' + CountyName
FROM dbo.OfficeCounty OC
INNER JOIN dbo.County C
ON C.CountyId = OC.CountyID
WHERE OC.OfficeID = O.OfficeID
ORDER BY CountyName
FOR XML PATH('')),1,1,'') AS countrynames
FROM dbo.Office O
ORDER BY 2
July 9, 2013 at 4:25 am
Hi Svetlana!
I think this is a nice introductory tutorial on using rCTEs.
However like a couple of folks that have already replied, I'd do it a little differently:
WITH Offices AS (
SELECT a.OfficeID, c.StateAbbr, c.CountyName, c.CountyID
FROM Office a
INNER JOIN OfficeCounty b ON a.OfficeID = b.OfficeID
INNER JOIN County c ON b.CountyID = c.CountyID)
SELECT a.OfficeID, StateAbbr=MAX(StateAbbr)
,countynames=STUFF((
SELECT ', ' + CountyName
FROM Offices b
WHERE a.OfficeID = b.OfficeID
ORDER BY CountyName
FOR XML PATH('')), 1, 2, '')
FROM Offices a
GROUP BY a.OfficeID
I believe that the looping string concatenation you're doing in the rCTE will impact your performance vs. something like the above.
To each his (or her) own I suppose.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 9, 2013 at 5:06 am
Beautifully illustrated, well written and easy to follow. Even though the rCTE method has been shown more than once to be somewhat slower than FOR XML PATH, there are some good ideas here.
I wonder if this method really is faster than a properly-written cursor?
I wonder how it compares to using a quirky update in a function?
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
July 9, 2013 at 5:53 am
CTEs come in particularly handy for hierarchical views (or table-valued functions if you prefer). I've been working on one app lately part of which allows supervisors to review data entry made by their staff and department heads can see and review everyone in their department, all from the same code just by passing in the id of the person accessing the data. Our organization is small enough performance isn't impacted very much.
Obviously you have to be careful about just how deep your hierarchy goes, etc., but for smallish hierarchical data they are quite nice.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 9, 2013 at 6:38 am
We can get the desired output using the following simple Query as well.
SELECT
O.OFFICEID,
O.STATEABBR,
STUFF((SELECT ',' + CAST(COUNTYNAME AS VARCHAR(8000)) FROM DBO.COUNTY B WHERE O.STATEABBR = B.STATEABBR
FOR XML PATH('')),1,1,'') AS COUNTYNAME
FROM (
SELECT O.OFFICEID,C.STATEABBR, OFF__STATE_COUNT = COUNT(1)
FROM DBO.OFFICE O
INNER JOIN DBO.OFFICECOUNTY OC
ON O.OFFICEID=OC.OFFICEID
INNER JOIN DBO.COUNTY C
ON C.COUNTYID=OC.COUNTYID
GROUP BY O.OFFICEID,C.STATEABBR
) O
ORDER BY O.OFFICEID DESC
July 9, 2013 at 7:41 am
ChrisM@Work (7/9/2013)
Beautifully illustrated, well written and easy to follow. Even though the rCTE method has been shown more than once to be somewhat slower than FOR XML PATH, there are some good ideas here.I wonder if this method really is faster than a properly-written cursor?
I wonder how it compares to using a quirky update in a function?
+1 @chrism-2
Thank you for the article. This was a very nice read and I am a big fan of rCTE's. Chris kind of summed up what I wanted to say (although I always forget about the quirky update) :hehe:
July 9, 2013 at 8:01 am
Oh no, we're toast! I've got this. *Keyboard clatter* Woah, how'd you do that? I'm a DBA...Booyah
That's great...
July 9, 2013 at 8:47 am
Great article. I did spot one tiny thing though. You said:
If you expect to have a list with more than 100 values ...
That is a bit misleading. I think that what you meant was the you can only recurse 100 levels. I have used recursive CTEs that resulted in many thousands of rows. The parent/child relationships only went 5 levels deep but there were lots of rows on each level.
Good work.
ATBCharles Kincaid
July 9, 2013 at 9:04 am
Or using CROSS APPLY:
SELECT OfficeID,
S.StateAbbr,
C.countrynames
FROM dbo.Office O
CROSS APPLY (SELECT TOP(1)
C.StateAbbr
FROM dbo.County C
INNER JOIN dbo.OfficeCounty OC
ON OC.CountyID = C.CountyId
AND OC.OfficeID = O.OfficeID
) AS S
CROSS APPLY (SELECT STUFF((SELECT ', ' + CountyName
FROM dbo.County C
INNER JOIN dbo.OfficeCounty OC
ON OC.CountyID = C.CountyId
AND OC.OfficeID = O.OfficeID
ORDER BY CountyName
FOR XML PATH(''))
,1,2,'') countrynames
) AS C
ORDER BY 2
July 9, 2013 at 10:45 am
same as
select officeid, StateAbbr , LEFT(ascountynames, len(ascountynames)-1) as ascountynames
from
( select distinct officeid, StateAbbr, (SELECT CountyName + ', '
FROM [County] tcm WITH (NOLOCK)
WHERE c.StateAbbr =tcm .StateAbbr FOR XML PATH('')) ascountynames
from [County] c inner join [OfficeCounty] oc on oc.CountyId= c.CountyId
) as t
July 9, 2013 at 12:02 pm
Thank you 🙂 ! I will definitely try FOR XML PATH. I came up with the recursive CTE solution after reading about another application of recursive CTEs, but perhaps performance wise, FOR XML PATH is faster.
July 9, 2013 at 2:33 pm
Why not to use CLR aggregate function that you can download from:
Izhar Azati
Viewing 15 posts - 1 through 15 (of 69 total)
You must be logged in to reply to this topic. Login to reply