This page serves as a compendium for all blog posts dedicated to the my talk entitled A Slice of Time: Window Functions in SQL Server.
A Slice of Time: Wrapping Up
This is part eleven in a series on window functions in SQL Server. What Did We Learn? Over the course of this series, we learned a lot about window functions. We started off with an overview of the concept and then moved on to the various types of functions (aggregate, ranking, offset, statistical, and ordered…
Keep readingA Slice of Time: Batch Mode Power-Ups
This is part ten in a series on window functions in SQL Server. The Need for Speed SQL Server typically operates in row mode, which means that an operator processes one row at a time. This sounds inefficient, but tends to work out pretty well in practice. However, something which may work out even better…
Keep readingA Slice of Time: Indexing and Window Functions
This is part nine in a series on window functions in SQL Server. POC: the Rule of Thumb If you’ve been around the block with window functions, you’ve probably heard of the POC indexing strategy: Partition by, Order by, Covering. In other words, with a query, focus on the columns in the PARTITION BY clause…
Keep readingA Slice of Time: Window Function Limitations
This is part eight in a series on window functions in SQL Server. Not All Roses So far, we’ve seen some really cool things we can do with window functions. That said, there are some limitations with these windows. Some of them are built into how window functions work and others are incomplete implementations of…
Keep readingA Slice of Time: Good Use Cases
This is part seven in a series on window functions in SQL Server. The Road So Far To this point, we’ve looked at five classes of window function in SQL Server. I’ve given you a couple of solid use cases, but for the most part, we’ve focused on what the classes of window functions are.…
Keep readingA Slice of Time: Ordered Set Function(s)
This is part six in a series on window functions in SQL Server. One of a Kind: The Ordered Set Function As of SQL Server 2019, there is only one ordered set function: STRING_AGG(). I like STRING_AGG() a lot, especially because it means my days of needing to explain the STUFF() + FOR XML PATH…
Keep readingA Slice of Time: Statistical Functions
This is part five in a series on window functions in SQL Server. Reviewing the Statistical Functions The next set of window functions we will look at are the statistical functions. There are four of them: PERCENT_RANK(), CUME_DIST(), PERCENTILE_CONT(), and PERCENTILE_DISC(). PERCENT_RANK and CUME_DIST PERCENT_RANK() is a fairly straightforward function: rank each window based on…
Keep readingA Slice of Time: Offset Functions
This is part four in a series on window functions in SQL Server. Offset Functions in SQL Offset functions are another class of window function in SQL Server thanks to being part of the ANSI SQL standard. Think of a window of data, stretching over some number of rows. What we want to do is…
Keep readingA Slice of Time: Ranking Functions
This is part three in a series on window functions in SQL Server. The Original Windows Last time around, we looked at aggregate window functions, which I think are the easiest for someone brand new to the concept to understand. Today, we’ll shift to the first window functions introduced to SQL Server in 2005: ranking…
Keep readingA Slice of Time: Aggregate Functions
This is part two in a series on window functions in SQL Server. Last Time on 36 Chambers In the prior post, we looked at the anatomy of a window function and said that it looks kind of like this: Now we’re going to look at a specific class of window functions: aggregate functions. I…
Keep readingA Slice of Time: Overview
This is part one in a series on window functions in SQL Server. What is a Window Function? A window function is, quite simply, a function applied over a window of data. If that doesn’t sound impressively reductionist, I don’t know what will. So let’s unpack this a little bit further. A window function operates…
Keep reading
36 Chambers - The Legendary Journeys: Execution to the max! 