Kākāpō!
One line I see over and over again — I’ve probably said it too when I was young and needed words to fill space — is that CTEs make queries more readable.
Personally, I don’t think they make queries any more readable than derived tables, but whatever. No one cares what I think, anyway.
Working with clients I see a variety of query formatting styles, ranging from quite nice ones that have influenced the way I format things, to completely unformed primordial blobs. Sticking the latter into a CTE does nothing for readability even if it’s commented to heck and back.
There are a number of options for formatting code:
Good Standing
Formatting your code nicely doesn’t just help others read it, it can also help people understand how it works.
Take this example from sp_QuickieStore that uses the STUFF function to build a comma separated list the crappy way.
If STRING_AGG were available in SQL Server 2016, I’d just use that. Darn legacy software.
The text I added probably made things less readable, but formatting the code this way helps me make sure I have everything right.
- The opening and closing parens for the STUFF function
- The first input to the function is the XML generating nonsense
- The last three inputs to the STUFF function that identify the start, length, and replacement text
I’ve seen and used this specific code a million times, but it wasn’t until I formatted it this way that I understood how all the pieces lined up.
Compare that with another time I used the same code fragment in sp_BlitzCache. I wish I had formatted a lot of the stuff I wrote in there better.
With things written this way, it’s really hard to understand where things begin and end and that arguments belong to which part of the code.
Maybe someday I’ll open an issue to reformat all the FRK code ?
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Kākāpō!
Thank you for your interest in Darling Data.
Your comment is important to us.
I knew it! I was worried for a moment when it said I was in a queue and played Weezer hold music.
Wearing a tuxedo will get you to the front of the line.
I have a question not about the formatting bit (which, to be fair, is most of the point of your post), and more about the STUFF() idiom you’re using. I usually see this written as STUFF((«some query» for xml path(”)), 1, 2, ”). I see you’re asking to get XML-typed data out and then using .value(). What advantage does that have?
For me, it’s control over the output datatype so things don’t get implicitly converted to/from n/varchar. It’s an issue I remember hitting a very long time ago and sticking with this ever since.
Hey Ben, the other thing is entitization. Compare:
SELECT
(
SELECT N'Fish & chips'
FOR XML PATH (''), TYPE
).value('./text()[1]', 'nvarchar(max)')
SELECT
(
SELECT N'Fish & chips'
FOR XML PATH ('')
);
Ooh… quoting/encoding issues. That’s definitely a problem. Thanks, Pau!
I agree and concur wholeheartedly. Let’s not get started on the readability (or lack thereof) of leading commas…
For sure. I understand the convenience factor of leading commas when you’re quoting things in and out, but they look like garbage.
There is another tool to add to your list. It sits above the shoulders of the hands at the keyboard.
I also hate the primordial goo. And while I don’t use a formatting tool other than the tab and spacebar keys, I have a block style that many of my coworkers emulate because it is readable. Of course in public I get “Why do you capitalize keywords? You are yelling COBOL at us.” It is for the same reason we require literals to be in single quotes: legibility. Unless you are a 1 person shop, most of the code you read and maintain will have been written by someone else.
And while I am slowly being won over to the leading quote (for editability, not legibility) I still say “;WITH” IS NOT A THING. Grow up and terminate statements properly.