A Fine Slice Of SQL Server
Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
Their actual execution plans are wildly different:
Click through to learn when the ordering in a GROUP BY clause matters and when it doesn’t.
Erik Darling plays tic-tac-toe:
The problem is really the stuff that people stick into views. They’re sort of like a junk drawer for data. Someone builds a view that returns a correct set of results, which becomes a source of truth. Then someone else comes along and uses that view in another view, because they know it returns the correct results, and so on and so on. Worse, views tend to do a bunch of data massaging, left joining and coalescing and substringing and replacing and case expressioning and converting things to other things. The bottom line is that views are as bad as you make them.
The end result is a trash monster with a query plan that can only be viewed in full from deep space.
Read on to learn the use cases for views and inline UDFs, as well as a few important notes regarding performance of each. Views are like mogwai: they’re fine as long as you never get them wet and never let them eat after midnight. The problem is, far too many companies are apparently the business equivalent of all-you-can-eat buffets at water parks.
Inline user-defined functions are like patenting a device that lets you shoot yourself in both feet with one pull of the trigger. Which, if I understand things correctly, means you’ll need a Form 4 for each inline UDF.
Comments closedKevin Wilkie gets the drop on us:
When you’re working between SQL Server and Snowflake, there can be a lot of crossover that may make you forget what system you’re working in. Sometimes it’s close, but not close enough.
Today, let’s go over something that should be rather simple – removing old objects that we shouldn’t need any longer.
Read on to see how the two data platform technologies differ in this regard.
Comments closedMuch like joins and Venn diagrams, anyone who thinks they have some advanced hoodoo to teach you about common table expressions is a charlatan or a simpleton. They are one of the least advanced constructs in T-SQL, and are no better or worse than any other abstraction layer, with the minor exception that common table expressions can be used to build recursive queries.
As I read through the post, I kept wanting to disagree with Erik more than I do. My short form is, I aesthetically prefer common table expressions to subqueries. But that doesn’t make CTEs faster.
Comments closedAndy Brownsword uses the QUOTENAME() function:
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the
QUOTENAMEfunction.
My personal preference is to use exec sp_executesql for parameterization instead, simply because it’s a lot harder to mess up.
Erik Darling shares some advice:
I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:
- When I have a list of literal values
That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.
I’m typically a lot more flexible about using IN, though I do agree with NOT IN: that clause is usually more trouble than it’s worth.
In our data hungry world, knowing how to effectively load and transform data from various sources is a highly valued skill. Over the last couple of years, I’ve learned how useful many of the data manipulation functions in PostgreSQL can supercharge your data transformation and analysis process, using just PostgreSQL and SQL.
For the last couple of decades, “Extract Transform Load” (ETL) has been the primary method for manipulating and analyzing the results. In most cases, ETL relies on an external toolset to help acquire different forms of data, slicing and dicing it into a form suitable for relational databases, and then inserting the results into your database of choice. Once it’s in the destination table with a relational schema, querying and analyzing it is much easier.
I call out CROSS JOIN LATERAL (or any kind of lateral join) here because it’s the ANSI equivalent of T-SQL’s APPLY operator, and I’ve already pointed out once today that I’m a huge fan of APPLY.
Erik Darling gets an auto-link for talking about my favorite operator:
I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.
One of the most common questions I get is when developers should consider using apply over other join syntax.
The short answer is that I start mentally picturing the apply syntax being useful when:
To learn when, you’re going to have to read the whole thing. And, if you want to learn even more about it, I have a talk on the topic that might be of interest.
Comments closedAndy Brownsword breaks out the stopwatch:
The
TOPclause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. In contrast, when usingMAXwe’re applying a function to our data to select the largest value from our data.Let’s dive into some examples with the StackOverflow data, specifically the Votes table.
Read on for several scenarios and how the two perform. Things get a bit more complicated as you introduce other tables in joins and similar additional factors, but this gives you a good foundation for comparison.
Comments closedErik Darling plays Descartes in his spare time:
Let’s talk about two of the most overlooked and undervalued facilities in the SQL language: EXISTS and NOT EXISTS. Perhaps they’d get more traction is they were called THERE and NOT THERE, but but it would be perilously easy to start confusing your WHERE and THERE clause.
Often besmirched by someone who read a blog post about MySQL in 1998 as “subqueries, which are slower than joins”, they’re two of the most useful things you can grasp and use regularly.
This post does a great job of explaining when you’d want to use EXISTS and NOT EXISTS.