Blog - Page 6 of 25 - SQLServerFast

T-SQL Tuesday 169 – Thank you, community!

December 12, 2023

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

December 7, 2023

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

December 2, 2023

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…

Plansplaining part 24 – Windows on the fast track

November 21, 2023

Hugo Kornelis

No Comments

This is part twenty-four of the plansplaining series. In the previous part, I explained the execution plans for basic window functions, with and without a window frame. Especially the latter group performed quite poorly in the examples. So let’s now look at an optimization that SQL Server can apply to most cases, that prevents this rather bad scaling. Running totals The examples in the previous post all used a window frame that started at (some number) PRECEDING. And as long as (some number) is quite low (and the end of the frame is not defined as (huge number) FOLLOWING), the…

Plansplaining part 23 / T-SQL Tuesday 168 – Window functions

November 14, 2023

Hugo Kornelis

I skipped the last two T-SQL Tuesdays. But this month, I will participate again. The monthly blog party is once more hosted by Steve Jones. His chosen topic is: Window Functions. In his invitation, Steve specifically asks for examples where Window functions provided a neat solution to a real world problem. Well, sorry Steve, but I am not going to do that. But your invitation did inspire to me write about the execution plans for these window functions. And there is, in fact, so much to write about it, that this is just the first part. So that makes this…

Black Friday returns!

October 17, 2023

Hugo Kornelis

No Comments

November approaches again. For Europeans, that is a month like any other, for Americans, November means: Black Friday, with huge discounts! Two years ago, I decided to try an experiment. All of November 2021, I offered a 50% discount on all prices for the SQLServerFast Execution Plan Video Training – both individual videos, as well as the already cheaper full-block purchase. The results were better than expected, and certainly reason to repeat this. Last year, I tried something else: a Black Friday Charity, where the 50% discount was not offered to you, as the customer, but donated to charity. Though…

An update on MERGE

September 29, 2023

Hugo Kornelis

The MERGE statement compares source and target data, and then inserts into, updates, and deletes from the target table, all in a single statement. This statement was introduced in SQL Server 2008. I liked it, because it allows you to replace a set of multiple queries with just one single query. And while a statement with that many options necessarily has a more complex syntax, I still believe that, in most cases, a single MERGE statement is easier to read, write, and maintain, than a combination of at least an INSERT and an UPDATE, often a DELETE, and sometimes first…

T-SQL Tuesday 165 – Job titles vs actual work

August 8, 2023

Hugo Kornelis

It’s the second Tuesday of the month again, so that means that it’s time for another T-SQL Tuesday. Our host is Josephine Bush, and she wants us to talk about the often confusing job titles in the data world. And while I will (of course) share my thoughts on that topic, I have decided to also include a short rant about a very much related topic: tracks on conferences. Those who have submitted conference talks before will probably immediately understand why I call that a very much related topic. The rest will have to read on! Job titles I advertise…

Plansplaining, part 22. SQL Graph (part 3)

July 20, 2023

Hugo Kornelis

Welcome to part twenty-two of the plansplaining series, where we will continue our look at execution plans for graph queries. We started this series by investigating the hidden columns in the internal structure of graph tables. In the second part, we looked at how those hidden columns are used in the execution plan for a relatively basic graph query. Time to up our game and tackle the SHORTEST_PATH function, introduced in SQL Server 2019, that can be used to make SQL Server search a graph iteratively or recursively to find the shortest possible path from one node to another. Sample…

T-SQL Tuesday 164 – Optimizing for readability or for performance

July 11, 2023

Hugo Kornelis

This month’s T-SQL Tuesday is hosted by Erik Darling. His challenge is to write about “Code That Made You Feel A Way”. I was unsure when I read this assignment. I don’t recall ever getting emotional over code. Sure, I can feel pretty smug when I have optimized a query to slash its execution time by 99%, or when I see that the program code to solve a complex issue actually is pretty clean. But in those cases, it’s my achievement that makes me proud, not the code. And, yes, I can also get quite upset when I encounter code…

T-SQL Tuesday 169 – Thank you, community!

Plansplaining part 25 – Windows without upper bound

Execution Plan Video Training – pre-orders open

Plansplaining part 24 – Windows on the fast track

Plansplaining part 23 / T-SQL Tuesday 168 – Window functions

Black Friday returns!

An update on MERGE

T-SQL Tuesday 165 – Job titles vs actual work

Plansplaining, part 22. SQL Graph (part 3)

T-SQL Tuesday 164 – Optimizing for readability or for performance

SQLRally Nordic 2012 – session material

Bleeding Edge 2012 – session material

Why does SQL Server not compress data on LOB pages?

Jetzt geht’s los – speaking in Germany!

Book review: SQL Server Transaction Log Management

Decks and demos – Session material for Silicon Valley Code Camp

SQLRally and SQLRally – Session material

Understanding Execution Plans

Parameterization and filtered indexes (part 1)

Parameterization and filtered indexes (part 2)

Hugo Kornelis

Subscribe via Email

Meta

Archives