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:
Formatting your code nicely doesn’t just help others read it, it can also help people understand how it works.
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!