SQL Server Query Transformations With ROW_NUMBER And ANY Aggregates
Video Summary
In this video, I wanted to share a fascinating query plan transformation that I recently stumbled upon while working on some SQL Server queries. I decided to dive into it after noticing a difference in how two seemingly similar queries were executed by the optimizer. The first query used a row number function and referenced it in the outer select statement, resulting in a standard window aggregate filter operator in the plan. However, the second query did exactly the same thing but without referencing the row number function in the outer select, leading to an unexpected transformation where the optimizer grouped everything together using an any aggregate instead of the usual windowing functions. While this didn’t have a significant impact on performance (both queries finished within milliseconds), it was still quite surprising and demonstrated how SQL Server can optimize query plans in ways we might not expect. I also wanted to give credit to Paul White, who had already written about this phenomenon years ago, highlighting the specific conditions under which such transformations occur. This video is part of my ongoing series on writing efficient queries and understanding query execution plans better.
Full Transcript
Erik Darling with Darling Data. And I know that not really much in the grand scheme of things, but my YouTube channel recently hit, did not cross yet, but hit the 3,000 subscriber mark. And so we’re going to have a 3,000 second moment of silence to commemorate. Just kidding. We’re not going to do this. We’re going to talk about a funny query plan transformation that I ran into the other day. And that I hadn’t actually come across before, at least that I’d noticed, that I thought was pretty cool. And there I was feeling all like flying high, like found this cool thing. Hey, check this out. And of course, Paul White wrote about it when I was like five years old. So we’re going to, I’m going to show you what it is. And then we’re going to go look at his post for a minute and I’ll give, I’ll give credit where credit is due. It’s all of our fathers. So these queries are probably way more complicated than they need to be, but they’re just what I had that produced it. So that’s what you’re getting. I’m going to zoom into where these queries are different, just so you understand a little bit about why I thought this was cool, and why I was surprised by the query plan that I got. So this first query, in the inner part of the query, I have a row number function, aliased is n. That’s this whole thing here. And then the first query, and this is the only part that’s going to be different from the second query, I reference the windowing function in the select list. In the second query, I do exactly the same thing right here.
Actually, I should show you one other thing is that at the very end of the query, both of these filter down to where n equals one, right? So in the second query, I don’t reference that column in the select list. You can see that’s where it’s different. I end that with user ID there, but I still do the filtering down here, right? So where y.n equals one. So the only difference between these two queries is that in one, I reference the row number function and the the outer select and the other one, I don’t, but they both still filter down to where y.n equals one. So let’s look at the query plans for these. In the first one, I have what I consider to be a pretty normal looking query plan for a query that has a windowing function in it, at least for batch mode, right? Because I have my window aggregate operator here. If this were happening in row mode, there’d be a segment and a sequence project.
And, you know, that would be what’s making the row number happen. Right? And if you look over here, I have a filter operator and that filter operator is going to be what brings the result set down to where the row number equals one. That expression 1010 in this context is the row number function. So filtering that down to one is what happens there. So we generate the row number, we filter it to one.
What happened in the second query that surprised me quite a bit, and this isn’t a performance thing necessarily, this is just a cool transformation thing. You see both queries finish in just, well, within like a millisecond of each other. 1.127 seconds and 1.128 seconds. So this isn’t a performance thing. This is just kind of a cool optimizer trick. So that’s that.
But if we look at the second query, notice that there’s no window aggregate operator and there’s no filter operator. Right? If we look at these two plans kind of side by side, there’s like a window aggregate filter up here. And down here, there’s only a hash match aggregate.
Hello, my name is Eric. I speak for a living. So if we zoom in on this plan a little bit, you’ll see that we don’t have any of that stuff. All we have is the hash match. And the details of the hash match are pretty cool.
Now, another thing that I want to point out, this is what really like freaked me out at first. I was looking at this, I was like, but how did it get down to the right number of rows? Because when you look at it, right, like this query up here at the filter, it gets down to 1092436.
The estimate’s close enough, you know, horseshoes, hand grenades, government. And down here, the hash match aggregate is what gets us down to 1092436. So this does produce the right number of rows. It just does it in a different way.
So if we zoom, if we look at the hash match aggregate, we’re not going to get anything too interesting from the tooltip. But if we go into the properties and we line this up in a way that does not make us look like a vicious idiot, we’re going to see a couple things. We have the hash key build and there’s nothing really interesting in here.
This is just showing unions and whatnot. But if we look in the defined values up here, we have this any aggregate, right? Scalar operator equals any.
Interesting stuff. So down here, again, where stuff is not all that interesting, where we just have these stupid union names, these union columns are what’s in the column expressions from the row number. So like the hash key is building up the columns in the partition by and order by that I have in my row number function.
And then this any aggregate is what is giving us matches or just giving us whatever comes out of the text column in the comments table. So I do have to go back to the query a little bit to explain what I’m doing, where I am trying to get, I’m trying to figure out a unique set of values for user ID, score, creation date, and post ID. But in the select, I also have this text column.
So what I was trying to get away from, this is going to be in a blog post of mine coming up about union and union all and part of my how to write queries correctly series. So what I was trying to get away from is just doing a union between two different queries that hit the comments table, because deduplicating the, like getting a distinct result set with the text column involved was slow or slower, right? It’s like, you’ll see in the post, but so that’s what I’m trying to get away from.
And so I figured, well, I don’t need to get rid of, I don’t need to like deduplicate the text column because like, like that’s, that’s like distinct enough on its own, right? Like if I find where a user, if I find the user ID score, creation date, and post ID, that gives me a unique enough combination to figure out if this is unique. So SQL Server does all this stuff, and it groups all this stuff, and then it uses that any aggregate to just get whatever exists in the text column.
So that was a performance thing. This particular example where one uses the row number and the filter to do it, and the other one just groups everything together and then uses the any aggregate on the text column. That is not really a performance thing.
It’s just a cool transformation, like I said. So if we go over to Paul’s post about it, there is a section. I appreciate that section heading. What magic is this?
It is, here are some rules. So this, this is a simplification rule. And there are some particular things that you need to qualify for in order for this particular transformation to kick in. The filter has to, has to restrict the row number to one.
If you do like greater than one or less than two or something like that, probably not going to get what you want. The expression added by the sequence project must not form part of the query result. So that, that is why it was different from one to the other.
Because when you select the row number, it does the whole window aggregate filter thing. And, but when you just have it in the where clause, SQL Server is like, oh, let me just give you any old thing. Uh, you have to partition by in order by the grouping columns, which I did, right?
That’s just by magic. And the non-grouping columns must be constrained, not null. I read that wrong at first. And I was like, it was like, if you go look at the comments table layout and stack overflow.
Like, I was like, I read that, I read that wrong the first time and I was like, but wait, but score and user ID are nullable, but maybe because I’m restricting the null zone. But then I realized it was the non-grouping column, which is the text column. And that is indeed constrained, not null.
So just sort of by some happy, happy happenstance, this happened to work out and get that transformation, which I thought was cool, which turns out to just be old news. So anyway, if you, dear watcher, dear viewer, I guess, I like watcher better. I like to think most of my audience is creeps.
If you, dear watcher, ever come across a query plan and you write your row number function or your windowing function and you don’t see the usual stuff that you would see when you have, when you write windowing functions, one thing that might be happening is you might be getting this cool transformation with the any aggregate. So just something to keep in mind out there. I don’t really have much more to say about it. I’ll link to Paul’s post in the notes for the video so you can go read much more detail what’s going on.
But I just thought it was something kind of neat and cool. And if it surprised me, I’d like to think that it would surprise a lot of other people to see. So kind of a neat thing. Not terribly informative, but just to me at least, terribly interesting. So anyway, usual spiel here. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. Probably not too much.
Maybe just maybe maybe you learned something is a little bit ambitious for this video. Maybe just observed something or are now aware of something is probably about the limitation that we can put on this one. Right. So good, good. Anyway, if you liked the video, give it the old thumbs up. If you like this sort of content, please subscribe to the channel so we can have a 4000 second moment of silence at some point. And once again, thank you for watching. I think it is Indeed.
I think many of you worked. I think it wasilleurs the last week even because of sports
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.