SQLServerFast

Plansplaining

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…

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…

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…

Plansplaining part 24 – Windows on the fast track

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

Hugo Kornelis
7 Comments
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…

Plansplaining, part 22. SQL Graph (part 3)

Hugo Kornelis
No Comments
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…

Plansplaining, part 21. SQL Graph (part 2)

Hugo Kornelis
2 Comments
Welcome to part twenty-one of the plansplaining series, where we will continue our look at execution plans for graph queries. In the previous post, we looked at the internal structure of node and edge tables, and discovered that they have a few hidden columns. Now let’s look how those columns are used in graph queries. Sample data As mentioned in my previous post, I’m using sample tables and data from Microsoft’s documentation for these posts. You can find the script to create and populate the tables here, or in my first post on SQL Graph. Simple query Let’s start with…

Plansplaining, part 20. SQL Graph (part 1)

Hugo Kornelis
2 Comments
Welcome to part twenty of the plansplaining series. It has been a long time since I last wrote a plansplaining post, partly because of my health, but also for a large part because I was out of ideas. But recently I decided to dig a bit deeper into a feature that was released in SQL Server 2017 and that I had so far not played with: SQL Graph. SQL Graph is the name for a set of features, introduced in SQL Server 2017 and extended in SQL Server 2019, that bring graph database functionality into SQL Server. See here for…

Plansplaining, part 19. Temporal tables (part 4)

Hugo Kornelis
2 Comments
Welcome to part nineteen of the plansplaining series, where I will finally wrap up the discussion on temporal tables. In the first three parts, we looked at execution plans for modifying data in a temporal table, and for basic and advanced temporal queries. In this last part, we’ll shift away for execution plans (so the plansplaining tag is perhaps a bit misleading) and look at what happens if you join data from multiple temporal tables. Sample data I once more use the same tables as in all previous post, but this time I want a bit more control over the…

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