Using a Recursive CTE to Generate a List

Question

Using a Recursive CTE to Generate a List

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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)

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 3

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

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

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 5

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

jennym Old Hand Points: 376 More actions · Answer 6

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!!! 🙂

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

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)

jennym Old Hand Points: 376 More actions · Answer 8

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?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

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)

jennym Old Hand Points: 376 More actions · Answer 10

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

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)

jennym Old Hand Points: 376 More actions · Answer 13

jennym

Old Hand

Points: 376

July 17, 2013 at 6:12 am

#1633206

Thanks!

smarinova SSC Enthusiast Points: 192 More actions · Answer 14

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

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