Using a Recursive CTE to Generate a List

  • izhar-azati (7/9/2013)


    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

    Mostly because I'm not going to download and install a .msi file from the internet from an untrusted source by an author I don't know. πŸ˜‰

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

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • smarinova (7/9/2013)


    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.

    I absolutely agree with Chris M. on this one. It's very well written with appropriate graphics. Well done!

    Shifting gears, I've not tested the recursive method that you've written for performance or resource usage, yet, but I agree that the FOR XML path will likely be quite a bit quicker.

    Also, 32K isn't the max limit on recursive CTEs. It the max descreetly definable limit. If you set the max to 0, it could run forever.

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

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Using a cursor:

    DECLARE myCursor cursor LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY

    FOR SELECT OfficeId

    FROM dbo.Office

    DECLARE @OfficeId int, @CountyNames varchar(3000), @StateAbbr varchar(5)

    SELECT @OfficeId OfficeId, @StateAbbr StateAbbr, @CountyNames CountyNames

    INTO #Results WHERE 1=0

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO @OfficeId

    WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @CountyNames = '' -- Initialise

    SELECT @CountyNames = @CountyNames + ', ' + C.CountyName,

    @StateAbbr = C.StateAbbr

    FROM dbo.[County] C

    INNER JOIN dbo.[OfficeCounty] OC

    ON OC.CountyId= C.CountyId

    AND OC.OfficeID = @OfficeId

    ORDER BY C.CountyName ASC

    INSERT INTO #RESULTS

    SELECT @OfficeId, @StateAbbr, STUFF(@CountyNames, 1, 2, '')

    FETCH NEXT FROM myCursor INTO @OfficeId

    END

    CLOSE myCursor

    DEALLOCATE myCursor

    SELECT * FROM #RESULTS ORDER BY 2

    DROP TABLE #RESULTS

    GO

    I know none of FOR XML, rCTE's and cursors perform that well so it would be interesting to see what method is better.

  • There are many ways to solve a problem. Some are better suited to situations than others.

    I think that the point of the article was to teach about recursive CTEs.

    ATBCharles Kincaid

  • There are many ways to solve a problem.

    Exactly. No one mentioned UDFs but that is a possibility too. Not a very good one though given some of the excellent responses here.

    CREATE FUNCTION [dbo].[udfMyConcatTitles]

    (

    @myString VARCHAR(255)

    )

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    DECLARE @STR VARCHAR(8000)

    SELECT @STR = ISNULL(@str+' / ', '')

    + OtherTitles

    FROM dbo.vwMyConcatTitles

    WHERE ObjectNumber = @myString

    RETURN @STR

    END

  • Thanks for the well written, clear, and simple tutorial. I use CTE's a lot as they've proven very useful when needing to manipulate data without write access in a database. I needed to do the opposite (parse a delineated list) and have tried recursion in the past, but the piece I was missing was the union of the anchor & recursive members of the CTE. With that tidbit from your article, and the counter example from a question on Experts Exchange (http://www.experts-exchange.com/Q_26033380.html), I dynamically pulled apart & was then able to link to a parsed field that end business users have been manually parsing in excel (I know, ouch). It's fast, fairly concise, and accurate.

    Appreciate you & all the others who take the time to publish your knowledge!!! πŸ™‚

  • jennym (7/15/2013)


    Thanks for the well written, clear, and simple tutorial. I use CTE's a lot as they've proven very useful when needing to manipulate data without write access in a database. I needed to do the opposite (parse a delineated list) and have tried recursion in the past, but the piece I was missing was the union of the anchor & recursive members of the CTE. With that tidbit from your article, and the counter example from a question on Experts Exchange (http://www.experts-exchange.com/Q_26033380.html), I dynamically pulled apart & was then able to link to a parsed field that end business users have been manually parsing in excel (I know, ouch). It's fast, fairly concise, and accurate.

    Appreciate you & all the others who take the time to publish your knowledge!!! πŸ™‚

    I strongly recommend not using an rCTE to "parse a delineated list". It's slow and it's resource intensive.

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

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

  • jennym (7/15/2013)


    Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

    It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. πŸ™‚ If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

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

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (7/15/2013)


    It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. πŸ™‚ If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

    While the 'find another job' advice seems a bit drastic πŸ˜‰ I actually had a note to self already to work with our dba to get a utility in place for splitting. Do you have any preferred examples for inline splitter's though, as I would be curiuos (maybe posted in another question or blog somewhere)? I read your Tally OH article but wasn't confident that's what you are driving at with your answer (and after that was strangely craving beer popsicles... ). Promise I'll drop it after this... appreciate the opportunity to pick your brain.

  • Jeff Moden (7/15/2013)


    jennym (7/15/2013)


    Agree completely, but wasn't sure a better alternative (the excel method has been worse for users than the CTE method so far). What would you recommend, assuming no write access to the database?

    It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. πŸ™‚ If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

    Wow! I feel that way on many days. Give me the tools and stay out of the way. Then I get confronted with a client database where I have no change authority and can only get at data through stored procedures. Frankly (and bluntly) it sucks.

    In cases like that I'm glad that I can write apps that use SQL as a resource and do all my string stuff in C#.

    ATBCharles Kincaid

  • jennym (7/16/2013)


    Jeff Moden (7/15/2013)


    It's probably not the answer you're looking for but I'd recommend talking with the DBA to get some decent utility code promoted so people can do their job. πŸ™‚ If that's not an option, perhaps finding a different company to work for... one that doesn't hogtie people trying to work. If that's not an option, then an effecient inline splitter is easy enough to make without resorting to rCTEs.

    While the 'find another job' advice seems a bit drastic πŸ˜‰ I actually had a note to self already to work with our dba to get a utility in place for splitting. Do you have any preferred examples for inline splitter's though, as I would be curiuos (maybe posted in another question or blog somewhere)? I read your Tally OH article but wasn't confident that's what you are driving at with your answer (and after that was strangely craving beer popsicles... ). Promise I'll drop it after this... appreciate the opportunity to pick your brain.

    You can incorporate the DelimitedSplit8K function as inline code instead of a function. Just take the code from the function and use it in a CROSS APPLY.

    Of course, the best thing to do would be to get the DBA to put the function into production... especially if you provide a beer popsicle as a peace offering. πŸ˜€

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

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks!

  • thank you!

    It is faster than a cursor and it's simpler to implement. The cursor processes one row at a time.

    using a quirky update in a function - what do you mean by that?

  • smarinova (7/23/2013)


    thank you!

    It is faster than a cursor and it's simpler to implement. The cursor processes one row at a time.

    using a quirky update in a function - what do you mean by that?

    rCTE's process one row at a time too, off the stack, which is quite different to cursor processing and quite a bit quicker.

    Here's a quirky update in a multistatement tvf. I wrote it with Dwain Camps a while back as a proof-of-concept; a set-based way to apply the same function n times to a variable, something like a nested REPLACE.

    ALTER FUNCTION [dbo].[IMF_QU]

    -- multistatement tvf with quirky update

    (

    @amount DECIMAL(28,2)

    )

    RETURNS @Results TABLE (strFormattedAmount VARCHAR(44))

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @ReverseAmount VARCHAR(44);

    SET @ReverseAmount = REVERSE(CONVERT(VARCHAR(34),(@amount)));

    DECLARE @MappingTable TABLE (n TINYINT UNIQUE);

    INSERT INTO @MappingTable (n)

    SELECT n FROM (VALUES (7),(11),(15),(19),(23),(27),(31),(35),(39),(43)) d (n)

    WHERE n <= (((LEN(REPLACE(@ReverseAmount,'-',''))-1)/3)*4)-1;

    UPDATE @MappingTable SET @ReverseAmount = STUFF(@ReverseAmount,n,0,',')

    INSERT INTO @Results SELECT REVERSE(@ReverseAmount)

    RETURN

    END

    GO

    SELECT *

    FROM (VALUES (3756.84),(3756),(375),(4884215.00),(21488.81),(22),(48955547787899554522)) d (Amount)

    CROSS APPLY dbo.IMF_QU (Amount)

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

Viewing 15 posts - 16 through 30 (of 70 total)

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