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…
A 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…
A 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…
A 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…
A 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.…
A 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…
A 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…
A 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…
A 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…
A 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: SELECT Col1, Col2, WINDOW_FUNCTION() OVER( PARTITION BY Col1 ORDER BY Col2 ROWS BETWEEN UNBOUNDED PRECEDING…
36 Chambers - The Legendary Journeys: Execution to the max! 