Using a Recursive CTE to Generate a List

  • Comments posted to this topic are about the item Using a Recursive CTE to Generate a List

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.

  • 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
  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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?

    โ€œ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

  • 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.

  • 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

  • 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:



    Everything is awesome!

  • 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...

  • 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

  • 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

  • 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


    Kindest Regards,

    egpotus DBA

  • 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.

  • Why not to use CLR aggregate function that you can download from:

    http://msftengprodsamples.codeplex.com/wikipage?title=SS2008%21String%20Split%20Table-Valued%20Function%20%28TVF%29&referringTitle=Home

    Izhar Azati

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply