Press "Enter" to skip to content

Expressive T-SQL with Equivalent Performance

Aaron Bertrand shows off one of the reasons I like SQL so much:

As an analogy, there are many routes you can take from New York City to Dallas. Some may be faster than others, some may be fewer miles but take longer, some are more fuel-efficient due to average speed limits, some more scenic, and some more toll-friendly. The beauty is that if you and I are independently planning the same trip, we can choose our routes based on our individual priorities. I may not like interstates, or I may prefer to drive more westerly until the sun starts setting, and you may want to see a particular tourist attraction, visit an uncle, or stop in a certain city.

A query is similar. Usually, performance is of utmost importance, but even that isn’t always true. When two or more queries give the same answer and have identical (or “close enough”) performance, the choice can come down to other factors, as mentioned above. I recently answered a question on Stack Overflow where the user was asking how to filter a grouping where an aggregate condition was true.

The fundamental insight here is that SQL is a 4th generation language, otherwise known as a declarative language: we tell the interpreter what we want and let it determine a path to get us there. By contrast, 3rd generation languages like C are imperative languages: it does what we tell it to do, no more, no less (until the compiler gets in there and re-writes our code to make it better…but these are academic ideas languages approach, not hard-and-fast mandates). There are benefits and drawbacks to either language depending on how creative you are and how good the interpreter/compiler is.