The Princess and the Recursive CTE

Once upon a time not so long ago in a little kingdom near the mountains there lived a Developer. She was a good and kind Developer who continually sought to improve upon her skills as a SQL database developer.

One fine day she was bestowed a colossal task of helping to convert an ancient, dying legacy system to a database SQL Server circa 2005. It was a complicated system whose conversion process had been in the works for what seemed like eons. Undaunted, she gave her best to this massive undertaking. Eager to learn more about SQL Server, this wondrous developer was soon adopted by the royal database court to support her in her time of need.

One dark dreary day, she approached SQL Princess with an enigma of her own. “Oh, Princess, do you have but a minute? I am in need of your wisdom for I have an issue possibly involving a cursor. I have heard cursors aren’t all that and was informed that you are known in some circles as the Slayer of Cursors. Hopefully, you may be able to lend a hand with this cursor.”

SQL Princess gasped, “A cursor did you say? Say no more!” she cried, “Where dost this foul filthy fiend reside? I shall smite it for thou post haste!”

“Calm down, Princess,” She said. “It tis not that bad. Here let me explain what we need it to do.”

The good Developer went on to explain the nature of this beast, “We have a fairly simple table that contains a description column. The data type for this column is varchar(max), and we do not know how long each record could be. The description needs to be cut into chunks of data 52 characters long. We weren’t sure how to accomplish this other than to use a cursor. This code is to run during a nightly process included with other tasks. Do you know of an alternative to the cursor we could use?”

Undaunted, SQL Princess thought for a moment and then said, “Hmm. Tis an interesting process you have. I do have an idea, though. Let me play with it, and I shall let you know.”

She then opened her mystical portal connecting to the test arena to play around with a few ideas. After consulting her SQL spell book and poking and prodding the code a bit, she exclaimed “Great Galloping Group By! I do believe I have it!”

SQL Princess quickly strode over to the good Developer eager to demonstrate her findings. “Good Developer, I have but pleasing news for you. I do believe I have an alternative! I now bequeath to you… a recursive CTE!”

The good Developer paused in her work and asked, “That sounds great and all, but what the heck is a recursive CTE?”

SQL Princess deftly responded, “Well, you may recall that a CTE, or Common Table Expression, is basically a temporary result set.  A recursive CTE basically references itself. It’s used quite commonly (pun sort of intended) to represent hierarchical structures such as who my minions are *cough*… err, the people I work with and who we all report to.”

With a flourish, she presented the code…

;with Results (sequenceNumber,royalDecreeID,stringValue,remaining) as
      (select 1 as sequenceNumber
            ,royalDecreeID
            ,left(decreeDescription,52) as stringValue
            ,right(decreeDescription, len(rtrim(decreeDescription)) - 55) as remaining
       from dbo.RoyalDecree
       where len(decreeDescription)>=52
      union all
      select sequenceNumber+1
            ,royalDecreeID
            ,left(remaining, 52)
            ,case when (len(Remaining) - 52 > 52)
                  then right(Remaining, len(Remaining) - 52)
                  else ''
                  end
      from  Results
      where len(Remaining) > 0
     )
select sequenceNumber
        ,royalDecreeID
        ,stringValue
from  Results
order by RoyalDecreeID,sequenceNumber;

The good Developer pondered this for a moment and asked, “Could you be so kind as to explain to me how this works?”

Being the ever so helpful DBA, SQL Princess pointed out the parts of the new code explaining as she went. “The first SELECT statement inside our oh-so neat CTE definition is called the anchor. This is the first level of our result set. The column I dubbed sequenceNumber starts out with a value of “1” and will be incremented for each set of descriptions in the recursive member which I will get to in a moment. Once a new record ID is reached, the sequence will start over.

Next we have the record ID to identify each record followed by a left function to get the first 52 characters. We’ll call this the StringValue for now. Following that is the Remaining column which contains the rest of the characters in the description. That second SELECT statement within the CTE after the UNION ALL is called the recursive member. This one references the name of the CTE itself and defines the recursion. This is where the sequenceNumber is incremented. Granted, it’s not the most elegant solution but it should suffice for the conversion process.”

The good Developer pondered the code a bit and ran it a few times. She then announced, “SQL Princess, I find this code disturbingly simple and elegant. It tis effin’ magic! It suits my needs perfectly! Thank you!”  

Satisfied with the results, SQL Princess replied, “You are quite welcome. I utterly enjoyed the challenge. Should you encounter other dilemmas and require assistance, do feel free to summon me.”

SQL Princess wandered back to her desk where her mystical portal awaited continuing her quest to slay unnecessary cursors and other foul SQL beasts. The good Developer worked happily on her code and endeavored to find more T-SQL challenges for the Princess.

The End

Advertisements

2 thoughts on “The Princess and the Recursive CTE

  1. Good morning/afternoon/evening/night.
    I have been enjoying your blogging, and am curious about the above CTE. The part that is stumping me is ‘right(decreeDescription, len(rtrim(decreeDescription)) – 55)’. It seems that you are stripping out the data 52 chars at a time, but are subtracting 55 chars at a time from the remaining string, so, to my unpracticed eye, it appears that you will be losing 3 characters from the string each time you split it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s