SQLServerFast

Posts

People queueing at the airport

Plansplaining part 29 – Introduction to cursor processing

Hugo Kornelis
6 Comments
This is already the 29th part of the plansplaining series, where I look in detail at interesting examples of execution plans in SQL Server. With this part, I’ll kick off another mini-series, this time on cursor processing Don’t use cursors! And yes, I know the mantra. Do not use cursors. They are slow. There is (almost) always a faster set-based alternative available. So why would I even waste blog space on cursors, when the only smart thing to do is to rip them out and replace them with a set-based alternative? Well, there are, in fact, many reasons. The “almost”…

Lots of events

Hugo Kornelis
No Comments
As I recently already wrote, I consider my leukemia diary closed now. And since today is exactly one year after my stem cell donation, I now officially have no restrictions on travel anymore. So let’s celebrate that by attending lots of in person events in the near future! SQLSaturday Austin My first trip is in two weeks, when I speak at SQL Saturday Austin, in Texas. On Friday March 8, I present a pre-con: Execution Plans in Depth. For the low price of just $125, you will receive a full day of expert teaching on execution plans, so that you,…

T-SQL Tuesday 171 – The end of a diary?

Hugo Kornelis
3 Comments
It’s February 2014, and it’s time to write a new T-SQL Tuesday post. Our host is Brent Ozar, and his chosen topic is the last ticket we closed. Well, sorry my friend. I’ve been ill. I have not been on active duty for well over a year now. And if you really think that I would still recall the last ticket I closed before getting my diagnosis and starting my long road to recovery, well, then I also have a bridge for sale! So I decided to do a less literal interpretation of Brent’s request. Isn’t life very much like…

Plansplaining part 28 – The curious case of the missing FIRST_VALUE function

Hugo Kornelis
1 Comment
In part twenty-eight of the plansplaining series, I’ll wrap up the six-part mini-series on window functions. After covering the basics, fast-track optimization, window frames ending at UNBOUNDED FOLLOWING, window frames specified with RANGE instead of ROWS, and LAG and LEAD, we will look at the LAST_VALUE and FIRST_VALUE analytical functions, and find that a function we would have expected to be available as an internal aggregate function does not exist at all! We’ll also find out how SQL Server works around that. LAST_VALUE We already encountered the LAST_VALUE internal aggregate function in the previous post, where it is used to…

T-SQL Tuesday 170 – Abandoned projects

Hugo Kornelis
No Comments
Welcome to the new year! And welcome to my first T-SQL Tuesday contribution of 2024. Reitse Eskens is our host, and he invites us to think back of a project that failed or was abandoned, and reflect on lessons learned. I love learning from mistakes. I often say that it’s important to learn from mistakes, so that we can make exciting fresh new mistakes the next time. That is, after all, way more exciting than constantly repeating the same mistakes over and over again. In this blog, I will reflect on not one but two abandoned projects. In one case,…

Plansplaining part 27 – LAG and LEAD

Hugo Kornelis
3 Comments
This is part twenty-seven of the plansplaining series, and episode five in the mini-series on window functions. The previous parts covered the basics, fast-track optimization, window frames ending at UNBOUNDED FOLLOWING, and window frames specified with RANGE instead of ROWS. In this post, we will shift our attention to the LAG and LEAD functions. Two functions that do not even accept a window frame specification. So why are they in this series? Read on to find out! LAG LAG and LEAD were introduced in SQL Server 2012. They require an OVER clause, but it can only specify PARTITION BY and…

Plansplaining part 26 – Windows with a ranged frame

Hugo Kornelis
1 Comment
This is part twenty-six of the plansplaining series. And already the fourth episode about window functions. The first of those posts covered basic window functions; the second post focused on fast-track optimization for running aggregates, and the third post explained how the optimizer works around the lack of execution plan support for UNBOUNDED FOLLOWING. But all of those were about OVER specifications that use the ROWS keyword. Let’s now look at the alternative, the RANGE keyword. RANGE instead of ROWS When a RANGE specification is used, then number PRECEDING and number FOLLOWING are not allowed. So this leaves us with…

T-SQL Tuesday 169 – Thank you, community!

Hugo Kornelis
No Comments
It is December. And it is Tuesday. So I guess it’s time for T-SQL Tuesday again. Our host this month is Kay Sauter, and his seasonally inspired topic is to call out the gratitude that we often feel towards other, but fail to express. This is a very easy task for me. If you’re a regular reader of this blog, then you will know that, during the last year, my focus was more on being ill and getting better than on dissecting execution plans (although I never really fully stopped doing the latter 😉). During all that time, the support…

Plansplaining part 25 – Windows without upper bound

Hugo Kornelis
No Comments
This is part twenty-five of the plansplaining series, and the third part that covers window functions. I have already explained how basic window functions work, with or without a window frame. When a window frame is present, a Window Spool operator will, for each row, return first that row, and then all rows that are visible in its frame. A Stream Aggregate then computes the requested aggregations on those rows. In the previous part, I then explained how “fast-track optimization” severely reduces the amount of work in cases where the window frame starts at UNBOUNDED PRECEDING. I also showed that…

Execution Plan Video Training – pre-orders open

Hugo Kornelis
No Comments
I have been working hard, the past months, to complete the next series of videos for the Execution Plan Video Training. The advanced level of block 3, Combining Data, is now ready. And from now until the end of the year, you can benefit from a nice pre-order discount. Advanced joins The basics of each of the four join operators are covered in the basic level of block 3. But there is a lot more to be told about these operators, and that is what the advanced level covers. There are four videos (“chapters”) in this level. Only one of…

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close