Learn T-SQL With Erik: Window Function Tricks for SQL Server 2022+
Video Summary
In this video, I delve into the latest enhancements to T-SQL in SQL Server 2025 and reflect on what Microsoft has or hasn’t done to improve it. While there are some minor additions like regex support and aware clauses, much of my focus is on the improvements from SQL Server 2022 that make working with window functions more efficient. I explore new features such as `ignore nulls` and `respect nulls` in `lag` and `lead` functions, which significantly simplify finding last non-null values. Additionally, I discuss shared window clauses, demonstrating how they can be used to reduce redundancy and improve query readability by allowing common window specifications to be defined once and reused across multiple window functions. Despite these useful additions, the overall sentiment remains that Microsoft has largely neglected T-SQL enhancements in recent releases, leaving SQL Server users with a sense of being left behind compared to other database systems.
Full Transcript
All right, so we are going to continue with a little bit of teaser material from the Learn T-SQL with Erik course. Again, that is still on presale price until after the summer when the advanced material drops. There’s a link to purchase it down below. It’s 250 bucks right now. It’ll last you for the rest of your life. And of course, if you are attending past data community summit and you’re coming to Kendra Little and I’s T-SQL pre-cons, you will get access to this material for free because it is completely free. It’s a companion material to what we will be teaching. So, you know, SQL Server 2025. There are release notes all over the place for it. And there is not a single worthwhile enhancement to T-SQL to talk about. Sure, we got regex. Okay. You have any idea how many people that’s going to screw up? Regex and aware clause. I mean, cool. Like, as a consultant, like, yeah. But as far as, like, things I’m excited about, nothing. I think, you know, one way, one way you can sort of judge how much Microsoft cared about a specific SQL Server release is by how much T-SQL has, been sort of alleviated of the many things that it has been missing for many years that are in the SQL standard. And this one is rather laughable. You know, I guess Microsoft is busy trying to get Fabric to catch up with Databricks.
So they have ceased trying to get SQL Server to catch up with, like, every other database on the planet. So, cool. Anyway, the only T-SQL enhancements that I have thought were kind of neat were back in SQL Server 2022 when Windows Functions got a couple neat new things. Windows Functions got nothing in SQL Server 2025. We have once again been left in the dustbin. We are on the shelf. We are not having a good time.
So, like, if you ever spend time, like, just like, I don’t know, like, if you read T-SQL blogs for fun, you read SQL Server blogs for fun, you may have found a particular brand of problem across posts over the years called the last non-null value. This, of course, this, of course, this, of course, did get easier with window functions. Before window functions, it was, forget it, like, queries would never finish. But even with window functions, it takes, like, multi-step queries in order to get the last non-null value for something.
So, if we run a query like this and we say, like, you know, we get the last commenter is lag user ID one over order by creation date, you’ll notice that there’s a lot of, you know, nulls in here. So, if we wanted to find the last non-null value, we would have to, like, we would have to essentially, like, like, run this query and then run another query to sort of, like, to get those other values. It gets very complicated very quickly.
What SQL Server 2022 added is a couple things that you can stick into, like, the lag lead window functions to either ignore nulls or respect nulls. Now, you can think of it what you will, that SQL Server Management Studio 21’s parser has a bunch of red squiggles in this query because it does not recognize the syntax from SQL Server 2022. All right.
So, we have SQL Server Management Studio 2021, which became GA, like, I don’t know, a couple months ago at this point. And we have SQL Server 2022, which came out, like, three years ago at this point. And the parser is still like, I don’t know what that means.
So, you know, we got dark mode. Okay. But I promise you that this query will run successfully.
What I’ve added to this query are the lines ignore nulls for this one and respect nulls for this one. So, ignore nulls makes finding the last non-null thing a lot easier because this will give you the last non-null value in the column. Right.
So, this ignore nulls just gives us the value that we want over and over again. Granted, this isn’t a very interesting data set, but the respect nulls, we get all of this stuff back. Right.
Now, forever, we have had the ability to pass in a third input to, like, lag and lead and stuff. I’m just going to spread this syntax out a little bit so it’s a little bit more obvious what I’m doing in here and why there are some rows that have a very strange big number in them.
And that is because I am adding a third optional input to the lag and lead functions, which give you a default value for anything that would have been produced a null because of the function. So, the results in here, you’ll notice that both of these lines have the integer maximum for them.
That just, that’s because we filled in a blank with that optional third parameter. Now, there’s other neat stuff that came out in SQL Server 2022 for window functions as well. Like, you can now have shared window clauses.
So, like, if you were writing window functions with, like, similar, like, window specifications in them, you would have to, like, write that over and over again and your queries could get very, very big with window function specifications. But now what you can do is you can say something like this, right?
Notice we’re just saying over x here, right? And typically, over x would be like, huh, what is x? Well, x is what we have defined down here.
This window x, right? We have, it’s almost, it almost looks like a CTE for your window function. Isn’t that scary? Right? Window x as partitioned by owner user ID, order by rows between unbounded proceeding and current row.
So, both of these window functions, sum and average, can share a common window clause. Now, would that Microsoft were so kind as to give us more neat enhancements to T-SQL like this in SQL Server 2025, you and I could be talking about much newer cool stuff.
Here we are, though. But what’s even neater, I think, about the common window clause is that you can actually stack them so that they inherit window clause specifications from higher up ones.
So, it almost looks like stacked CTE when you read them. So, here, notice that we’re not using x anymore. We’re using T-S and A-V, right?
So, this is the window specification for sum, and this is the window specification for average. And if we look down here, this is where I’m doing the magic work for this one. We have window x as partitioned by owner user ID, right?
And then we’re saying, comma, T-S as order by score between rows unbounded proceeding and current row. And then, just for, you know, a little bit of texture in the demo, A-V is ordering by score descending between unbounded rows proceeding and unbounded rows following.
So, they’re both going to partition by owner user ID, but then they’re both going to do something slightly different with the order by. So, score for this one is ascending.
Score for this one is descending. This one is going from the beginning of the results to the current row. And this one is going for the entire result set. So, unbounded proceeding and unbounded following. So, it’s like the entire thing is what we’re getting the average as.
And now, we can allow our window functions to not only share a window clause, but to inherit and share window clauses. So, we can get back even, we can, I don’t know, make much more interesting queries without, I don’t know, I guess that’s actually still kind of a lot of typing now that I think about it.
But, it saves you some space up here in the select list. It makes that cleaner and tidier. I’ll give it that much. So, that’s just a couple cool things from 2022. Hey, we got Regex.
Ding. Like and subscribe. All right. Cool. Thanks for watching. I hope you enjoyed yourselves. I hope you learned something. And I’ll see you over in the next video where we’re going to talk about some stuff that batch mode makes a whole lot faster.
So, we’re going to take a little break from being depressed about Microsoft’s abandonment of SQL Server generally. And we’ll talk about some stuff from back when they cared.
That’ll be a good time. Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.