Category: T-SQL

Brent Ozar keeps a list:

It’s hard to set absolute rules about, “Feature X should absolutely never be used.”

However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As we have a bigger discussion, it leads to the piece of code failing the code review, and going back to the drawing board for improvements.

I’ve definitely done 1-7 on purpose and 8 in protest. 9 is something I do when running ad hoc SQL in SSMS just to make sure that I didn’t goof too hard (and usually it’s a rollback rather than commit). But this is a really good list.

Erik Darling comes out of the gates with a hot take:

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.

I’m going to split the middle of Erik’s take. Yes, using common table expressions by themselves doesn’t make a query easier to read. And yes, a good formatting technique helps a lot in readable code. Once that’s taken care of, I do think that common table expressions can be a bit more readable than their equivalent subqueries, for the reason that they do a better job of separating logically distinct segments of code. In those situations, I can read through each common table expression, getting a feeling for what they’re doing and let them tell a story with a top-to-bottom progression. This technique is most effective when you need several common table expressions for a query. By contrast, when creating subqueries (which I tend not to do much) or derived tables with APPLY (which I do so often, I get the employee discount), the story’s a little more disjointed, as the eyes seem to bounce more frequently between the main query and the subqueries. And don’t get me started on subqueries in the SELECT clause—those are the equivalent of somebody telling a story and saying, “Hang on, now I have to tell this story so that you get what I’m talking about.”

But going back to my agreement, if your code looks terrible, it doesn’t matter what constructs you use—it’s not very human-friendly.

Itzik Ben-Gan wraps up a great series:

This is the fifth and last part in the series covering solutions to the number series generator challenge. In Part 1, Part 2, Part 3 and Part 4 I covered pure T-SQL solutions. Early on when I posted the puzzle, several people commented that the best performing solution would likely be a CLR-based one. In this article we’ll put this intuitive assumption to the test. Specifically, I’ll cover CLR-based solutions posted by Kamil Kosno and Adam Machanic.

Many thanks to Alan Burstein, Joe Obbish, Adam Machanic, Christopher Ford, Jeff Moden, Charlie, NoamGr, Kamil Kosno, Dave Mason, John Nelson #2, Ed Wagner, Michael Burbea, and Paul White for sharing your ideas and comments.

Read on for the conclusion.