Month: January 2024

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

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

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

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…

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.