The Project Graveyard

Published on: 2018-10-09

This post is a response to this month’s T-SQL Tuesday #107 prompt by Jeff Mlakar.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month’s Halloween themed topic asks to “…
share a story about a project you worked on or were impacted by that went horribly wrong.”


I’ve been fortunate enough to never have been part of a large disastrous project at work.  My projects always have a “fail fast” mentality, so they never build up to a point where they come crashing down in a death spiral.

But that’s not to say I haven’t experienced my own project horror story in my personal work.

A while back I made a goal to produce a quality SQL Server focused blog post and video every week.  Essentially this means I am starting a new small-scale project each week where I play the part of project manager, developer, analyst, etc… with a delivery deadline of every Tuesday morning.  While I’ve gotten better at this process over time, I have also failed to meet my personal goals numerous for a variety of reasons.

Scope Creep-y

In order to meet my weekly deadline, I need to stay laser focused on the topic I choose for that particular week.  If I get additional ideas while writing and start trying to incorporate them into my post (ie. scope creep), I inevitably miss midweek milestones and have to try to make up time elsewhere to make my deadline.

One instance of scope-creep I experienced earlier this year was when I was trying to write a post on how to build a table-driven validation system.

I’ve built many table-driven processes in the past so this seemed like it would be an easy topic to write about.  I started that week’s blogging process by building the demo templates that would include table structures, execution scripts, etc…

Instead of wrapping up my basic demos so I could move on to writing the actual post, I kept building out demos for more features: logging functions, parameterization, SQL injection protection, common performance problems, etc…

It was exciting to be building all of this out, but instead of creating one-week’s content, I realized I had started working on enough demos for several weeks of posts.  This wouldn’t have necessarily been a bad thing on its own; after all it’s nice to be a few weeks ahead on content creation.

However, I didn’t quite finish enough demos for any one post in particular, and due to some other life events I didn’t get back to working on my demos until Sunday afternoon.  Normally at that point I’d already have my demos done, a blog post written, a video filmed, and either a finished video edit that I’m uploading or getting really close to uploading to YouTube.  What I had instead was a bunch of half-finished SQL demos saved in a very rough outlined blog post.

The Project Graveyard

This isn’t the first time poor time management and scope creep has gotten me in trouble:

Some projects sent prematurely to the grave

I have several posts that I’ve invested a good amount of time into but never released because they are incomplete.  In almost all of these cases my problems stemmed from poor planning and scope creep.

In the case of my table-driven post, by late-Sunday afternoon I realized I was going to miss my weekly deadline goal if I continued with that post, so I scrapped the idea for now and quickly wrote and shot a different post on an SSMS trick instead.  It was discouraging to have to do that, but at the end of the day I was able to meet my weekly deadline even if it was with a different result than I initially expected.

You might be thinking, “Why not ignore deadlines and release the post later in the week/month?”  For me, I like my weekly deadlines because I like the creative challenges that come from having time constraints.  It forces me to limit my scope and work on different projects on a regular basis.  My goal from blogging and video making is to learn how to present information in a succinct manner so that my communication skills, both written and verbal, improve.  So while I can (and probably will) complete these posts at some point in the future, I treat them as failures for that particular week’s project.

And while failures aren’t particularly fun, they can wind up being great learning opportunities: after all, I haven’t gotten so off track due to scope creep ever since.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

FizzBuzz and Multiple Recursive Member CTEs

Published on: 2018-08-21

Watch this week’s episode on YouTube.

Last week I needed to write a recursive common table expression.  I’ve written them before, but it’s been a while and needed to visit the documentation to reference the syntax.

Instead of going straight to the examples, I decided to read into some of the details (since skipping the details really hurt me in last week’s post) and noticed this line that I had never seen before:

“Multiple…recursive members can be defined” – what????

I never knew you could have multiple recursive member statements in a CTE.  Heck, I didn’t even know what having multiple recursive members could do.

Since the documentation doesn’t talk about them beyond the one highlighted line above, I decided to create some examples to see if I could get them to work.

FizzBuzz

FizzBuzz is a programming puzzle that asks the solver to write a program that will list the numbers 1 to 100, displaying the word “Fizz” for any numbers that are a multiple of 3, “Buzz” for any multiples of 5, and “FizzBuzz” for any multiples of 3 and 5.

I decided to try and implement the FizzBuzz problem as both a single and multiple member CTE to see how the solutions would differ.

The Basic Recursive CTE

To start out, I decided to write a CTE that lists all numbers 0 to 100:

WITH c AS
(
    -- anchor member
    SELECT
        0 AS RowNumber
    UNION ALL
    -- recursive member
    SELECT
        c.RowNumber + 1
    FROM
        c /* the result of our last iteration */
    WHERE 
        RowNumber < 100
)

SELECT * 
FROM c;

The first SELECT statement in the CTE definition is known as the “anchor” member.  This query runs a single time and acts as the initial result that the recursive query acts on.

The second SELECT statement in the CTE definition is known as the “recursive” member.  This statement executes on the results of the previous execution (or on the results of the anchor member for the first iteration).

The recursive member will execute over and over again as long as it is still producing results.  Since our recursive statement is just adding 1 to the previous result, our recursive query would run forever – which is why we add the WHERE condition stop it from executing once we reach 100.

Our final SELECT statement returns the results of our recursive CTE, providing us with a neat list of numbers from 0 to 100:

Single Recursive Member CTE for FizzBuzz

Now that our basic recursive CTE is working, let’s make it solve FizzBuzz.  Here is our updated code:

WITH c AS
(
    SELECT
        0 AS RowNumber,
        'FizzBuzz' AS FizzOrBuzz
    UNION ALL
    SELECT
        c.RowNumber + 1,
        CASE 
            WHEN (c.RowNumber + 1) % 15 = 0 THEN 'FizzBuzz' 
            WHEN (c.RowNumber + 1) % 3 = 0 THEN 'Fizz' 
            WHEN (c.RowNumber + 1) % 5 = 0 THEN 'Buzz' 
            ELSE NULL 
        END
    FROM
        c
    WHERE 
        RowNumber < 100
)

SELECT
    RowNumber,
    FizzOrBuzz
FROM C
ORDER BY RowNumber;

First, we add a second column to our results to display the word “Fizz”, “Buzz”, or “FuzzBuzz”.

In the anchor member, we defaulted this value to “FizzBuzz”.  In our recursive member, we added a CASE statement to display the correct word.  The modulo operator (%) checks to see if the current row divided by 3, 5, or 15 results in a remainder – if the remainder is 0 then we know we found a multiple of that number.

This solution is pretty easy to read and provides the expected output for our FizzBuzz puzzle:

Multiple Recurisve Member CTE for FizzBuzz

Alright the moment we’ve been waiting for – the multiple recursive member CTE:

WITH c AS
(
    SELECT
        0 AS RowNumber,
        'FizzBuzz' AS FizzOrBuzz
    UNION ALL
    /* All rows not Fizz or Buzz or FizzBuzz */
    SELECT
        c.RowNumber + 1,
        NULL AS FizzOrBuzz
    FROM
        c
    WHERE
        c.RowNumber+1 <= 100
        AND (c.RowNumber+1)%3<>0
        AND (c.RowNumber+1)%5<>0
    UNION ALL
    /* Fizz rows */
    SELECT
        c.RowNumber + 3,
        CAST('Fizz' AS VARCHAR(8)) AS FizzOrBuzz
    FROM
        c
    WHERE 
        c.RowNumber+3 <= 100
        and FizzOrBuzz in ('Fizz','FizzBuzz')
    UNION ALL
    /* Buzz rows */
    SELECT
        c.RowNumber + 5,
        'Buzz' AS FizzOrBuzz
    FROM
        c
    WHERE
        c.RowNumber+5 <= 100
        and FizzOrBuzz in ('Buzz','FizzBuzz')
)

SELECT
    RowNumber,
    STRING_AGG(FizzOrBuzz,'') AS FizzOrBuzz
FROM C
GROUP BY
    RowNumber
ORDER BY RowNumber

You’ll notice we have 3 recursive members: the first generates all rows up to 100 that are not multiples of 3 or 5, the second generates all rows that are multiples of 3, and the third statement generates all rows that are multiple of 5.

If we were to run SELECT * FROM c; after only making the mentioned changes, you’ll notice that it looks like things are mostly working, but that we have duplicates (and incorrect labeling) for rows that are multiples of 3 and 5:

The way I decided to fix that is by adding a STRING_AGG() function to the final SELECT statement, concatenating the outputs of rows with the same RowNumber. With that addition, our multiple recursive member CTE FizzBuzz solution is complete.

One thing to be aware of in the above solution: each of the recursive member statements will execute on the previous results of ANY recursive member statement, so we add the conditions “…and FizzOrBuzz in …” to force each recursive statement to run only on the output from its own previous result.  This feels like cheating a little bit, but it was the only way I could solve the problem I had defined.

Practical Examples and Further Reading

I had a hard time coming up with a practical uses for multiple recursive member CTEs.

I searched online for some examples but it doesn’t seem like many people have written about the topic.  One exception I did find was an article by Itzik Ben-Gan where he uses them to solve Lord of the Rings family trees (heh).

Honestly though, as excited as I was initially to learn that doing this is possible, I don’t know if/when I’ll ever use it.  I’m hoping I encounter a problem one day that can make use of multiple recursive statements, but who knows if that will ever happen.

If you have used multiple recursive member CTEs to solve a real-world problem before, leave me a comment – I’d love to hear about the scenario you used it in.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!


6 Techniques For Troubleshooting Your Code

Published on: 2018-08-14

This post is a response to this month’s T-SQL Tuesday #105 prompt by Wayne Sheffield.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month’s topic asks to share a time you ran into a metaphorical brick wall and how you worked it out.


Watch this week’s education film on YouTube.

The Problem: Trimmed JSON Values

Recently I was using FOR JSON PATH to generate a JSON string from a query to use in a web app:

SELECT TOP 100 *
FROM master..spt_values
WHERE type='P'
FOR JSON PATH

The resulting JSON string is 5,580 characters long.

The goal was to read this query result into my .NET app like so:

var jsonResult = db.Database.SqlQuery<string>("SELECT TOP 100 * FROM ... FOR JSON PATH");

Is this the best way to design every app/database interaction?  Probably not, but it was right for this scenario because I didn’t want to create models for all of the dynamic data I’d be returning.

Design decisions aside, my “brick wall” issue was that my “jsonResult” value was getting truncated around 2,000 characters instead of displaying the full 5,580.  The JSON string looked great in SSMS, but for the life of me I couldn’t figure out why the data was getting chopped off when read into .NET.

Time to Debug

What follows are the usual steps I take when debugging a problem that has me stumped.  I’ve turned these into a 1950s style educational film so you can laugh at my bad jokes while your learn.

1. Rubber Duck Debugging

The first thing I usually do when I hit a wall like this is talk myself through the problem again.

This technique usually works well for me and is equivalent to those times when you ask  someone for help but realize the solution while explaining the problem to them.

To save yourself embarrassment (and to let your coworkers keep working uninterrupted), people often substitute an inanimate object, like a rubber duck, instead of a coworker to try and work out the problem on their own.

Alas, in this case explaining the problem to myself didn’t help, so I moved on to the next technique.

2. Simplify the Problem

Breaking a problem down into smaller solvable parts can help sometimes.  I changed my query to return less data by switching to SELECT TOP 5 and seeing if .NET was still truncating the data.  It wasn’t! Mildly successful!

In this case though, I couldn’t really build off my simplified example.  As soon as my result passed ~2,000 characters, the JSON string was getting chopped off.

In this step I also figured out if I put my query into a derived table, my .NET code worked beautifully and returned the complete JSON string:

SELECT * FROM
(
SELECT TOP 100 *
FROM master..spt_values
WHERE type='P'
FOR JSON PATH
) t(c)

This was an ugly solution though and I didn’t like doing it.  I especially didn’t like it because I didn’t know why a derived table fixed the output.

3. Check the Internet

As great as the internet is, I try to limit myself to how much searching I do on it when troubleshooting.  Searching for an online solution can quickly devolve into wasting two hours with nothing to show.

I performed some cursory searches on Google, StackOverflow, various forums, blogs, etc… but didn’t find anything helpful (fun/sad fact: I searched for solutions again while typing up this post and now find plenty of solutions…who knows what I was doing wrong that day).

4. Ask a Friend

I love working through problems with others because I’m often amazed at how differently others approach a problem.  Often times this leads to a solution I would not have thought of on my own.  I especially enjoy hearing from people new to the subject area because they often have the most creative solutions due to not yet having become cynical and jaded from previous experience :).

I try to hold off on this option until at least trying all of the above techniques because 1) I hate breaking another person’s concentration 2) I feel like I learn better if I struggle through a problem myself.

And in this case shopping the problem around didn’t help – no one I talked to had a great solution.

5. Take a Break

After trying all of the above, I was out of ideas.  I took a break from the problem for the rest of the day, resolved to give it another try in the morning the morning.

6. RTFM

And the next morning, I had the idea to check the documentation to see what it said about the return type of FOR JSON PATH.

Embarrassingly, this one should be way higher on the list, and I’d like to say that it usually is, but for one reason or another I didn’t bother checking until this late in the game.

And wouldn’t you know it?  The last paragraph, of the last section, tells me exactly what I needed to know.

The documentation tells me that the JSON string will be broken up across multiple rows and my client app needs to concatenate them all together.  What I ended up doing is a simple String.Join():

var jsonResult = String.Join("",db.Database.SqlQuery<string>("SELECT TOP 100 * FROM ... FOR JSON PATH"));

There’s no explanation for why SSMS is able to concatenate these values together but other client apps have to manually do so, but at least I found my documented solution.

Conclusion

Even though I found a somewhat-satisfactory solution in the documentation, my fall back was going to be to use the ugly derived table solution discovered in step 2.  It was ugly, but at some point I would have to call it quits and settle with an ugly workaround rather than spend more time on troubleshooting.

Next time I’ll be sure to check the documentation earlier in the process and hopefully that will save me from some of the frustration I encountered in this particular scenario.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!