Controlling SQL Window Functions with Framing

Problem

I’ve used SQL window functions for years, but I’ve never fully understood framing. To truly master window functions, you need at least a basic grasp of framing and how to apply it to your queries. Framing is especially helpful when working with aggregate functions. How can you overcome this hesitation to try framing?

Solution

In this article, we’ll explore the concept of framing in window functions. We’ll compare the differences between the ROWS and RANGE clauses and discuss when to choose one over the other. We’ll also highlight common pitfalls of framing and whether it applies to all types of window functions. By the end, you’ll better understand how framing works with window functions, making it seem less complex.

Inside the Window Frame

Microsoft introduced framing in SQL Server 2012. Before that, you could use aggregates with OVER, but not with an ORDER BY or frame. That’s right, if you wanted a running total or moving average, you had to find another way. So, what exactly is framing? It’s a more precise way to control which rows you focus on within a partition.

Framing allows you to make T-SQL behave more like a procedural language without needing a cursor or WHILE loop.

Now, let’s look at the elements that comprise framing.

Framing Elements

In this section, we’ll examine each part that brings framing to life. Since framing mainly relates to aggregate and analytic functions, the examples I’ll share will focus on the aggregate type.

To get started, let’s create a simple table named PhotoFrames with five columns, and ten rows. Use the code below to follow along.

/* MSSQLTips.com */
DROP TABLE IF EXISTS dbo.PhotoFrames;
GO
CREATE TABLE dbo.PhotoFrames
(
    Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    SalesDate DATE NOT NULL,
    FrameStyle NVARCHAR(50),
    SalesPerson NVARCHAR(50),
    Amount DECIMAL(10, 2)
);
GO
INSERT INTO dbo.PhotoFrames
(
    SalesDate,
    FrameStyle,
    SalesPerson,
    Amount
)
VALUES
('2025-01-01', 'Rustic', 'Alice', 120.00),
('2025-01-01', 'Modern', 'Ben', 80.00),
('2025-01-02', 'Classic', 'Alice', 150.00),
('2025-01-02', 'Rustic', 'Ben', 200.00),
('2025-01-03', 'Modern', 'Cara', 90.00),
('2025-01-03', 'Classic', 'Dan', 220.00),
('2025-01-04', 'Rustic', 'Alice', 60.00),
('2025-01-05', 'Modern', 'Ben', 250.00),
('2025-01-05', 'Classic', 'Cara', NULL),
('2025-01-06', 'Rustic', 'Dan', 130.00);
GO

The code snippet below illustrates the basic structure of a frame for a running total.

/* MSSQLTips.com */
SELECT
    SalesPerson,
    SalesDate,
    Amount,
    SUM(Amount) OVER (ORDER BY
                          SalesDate ASC
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                     ) AS RunningTotal
FROM
    dbo.PhotoFrames
ORDER BY
    SalesDate ASC;
GO
Window Function Results

First, we have the window function you plan to use, SUM in this case. Next is the OVER clause, which can turn almost any aggregate function into a window function. Inside the OVER clause, you can optionally define a PARTITION BY, as I don’t in the example above, and then an ORDER BY. When you use a frame, you must include an ORDER BY; if you forget it, SQL throws an error.

We could stop there and never worry about the frame, which is why many people never explore it; they don’t need to. But if we did, I wouldn’t have written this article.

Next, let’s explore the two arguments in the OVER clause after ORDER BY that set the frame in motion.

RANGE and ROWS

Microsoft defines that the ROWS and RANGE clauses are used to limit the rows within a partition by defining start and end points. When you define an ORDER BY, SQL uses the default frame unit of RANGE, meaning that if you do not specify a frame, one is automatically used. The default frame appears as shown in the code below.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE focuses on the value of the row relative to the column specified in the ORDER BY clause. This is well demonstrated by the running total example, which clearly shows the difference. When using RANGE for a running total, rows with the same date (such as SalesDate in our example) are grouped based on their values.

Range

ROWS, on the other hand, emphasizes the physical row position within the partition rather than the value.

Some people dismiss RANGE for running totals, but not so fast. Consider a situation where you want to see daily transactions but prefer not to define a unique ORDER BY and are satisfied with only the daily total. This is a case where RANGE excels.

With ROWS and RANGE out of the way, let’s take a look at how to set boundaries.

Frame Boundaries

I don’t believe these are referred to as boundaries in the official Microsoft documentation. However, I think that’s a good way to categorize them because that’s precisely what they do: establish a boundary from the start to the end of the frame. In other words, they define the start and stop points for the partition.

Below are the five boundary settings:

  • CURRENT ROW: As the name implies, the current row.
  • UNBOUNDED PRECEDING: The first row in the partition.
  • UNBOUNDED FOLLOWING: The last row in the partition.
  • n PRECEDING: n rows before the current row.
  • n FOLLOWING: n rows after the current row.

The (n) in the examples above represents a numeric value. One important note is that RANGE does not support the numeric PRECEDING/FOLLOWING bounds.

Now, let’s apply all the information we just covered and look at a few simple examples. Try not to look up at the previous sections to see how much you remember.

Real-World Examples

What if we wanted to look at the current row and sum it to the previous row in the partition? Maybe we want to see the sum of sales for yesterday and today. Think about it for a minute before moving on.

Did you give it some thought? I hope so, and the code below should provide us with the correct results.

/* MSSQLTips.com */
SELECT
    SalesPerson,
    SalesDate,
    Amount,
    SUM(Amount) OVER (ORDER BY
                          SalesDate ASC
                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
                     ) AS RunningTotal
FROM
    dbo.PhotoFrames
ORDER BY
    SalesDate ASC;
GO

Below we can see if we add the current Amount with the previous row’s Amount we get the RunningTotal.

Window Function Results

Let’s do one more. What if you want to see a column with the sum of an entire partition’s sales? For this example, let’s partition by the SalesPerson column. Give it your best try before looking at the answer below.

/* MSSQLTips.com */
SELECT
    SalesDate,
    SalesPerson,
    Amount,
    SUM(Amount) OVER (PARTITION BY
                          SalesPerson
                      ORDER BY
                          SalesDate ASC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                     ) AS SumBySalesPerson
FROM
    dbo.PhotoFrames
ORDER BY
    SalesPerson,
    SalesDate;
GO

In this example, if we sum the Amount for each person we get the same SumBySalesPerson amount for each of their rows.

Window Function Results

Life Before Framing

The ranking window functions (ROW_NUMBER, RANK, DENSE_RANK, and NTILE) were introduced in SQL Server 2005. However, you couldn’t use ORDER BY (i.e., framing) in the OVER clause with an aggregate until SQL Server 2012. So, what did life look like before framing was introduced? Using the running total example, you would have needed a correlated subquery to return something like a running total or moving average.

I’m not saying that using a correlated subquery is a bad choice. There are definitely situations where it makes sense, but they don’t scale well when you want to add another type of frame to your SELECT clause. I’m glad that window functions were introduced, as they provide a simpler way to do this.

Additionally, an OUTER APPLY would have worked.

Pitfalls of Framing

Most great things in life have some downsides. There must surely be a few pitfalls when it comes to using framing. I can think of three. If you try to use one for a ranking function, SQL throws an error message.

Msg 10752, Level 15, State 3, Line 105
The function 'ROW_NUMBER' may not have a window frame.

Now I can’t really think of a reason where you would actually use framing in a ranking function, but I’m sure someone out there has a use case.

The other issue which may crop up is performance when you extend you’re window over a million or even billions of rows. I imagine that SQL keeping the frame in context is draining resources when the frame could expand over millions of rows. Or even if you have thousands of partitions.

I don’t know if I would call it a pitfall, but sometimes people think that framing actually changes the partition, which it doesn’t. For example, no matter which rows you’re looking at, defined by the BETWEEN, they are still viewed only within the current partition.

Key Points

  • If you plan on using window functions to their fullest potential, you must have at least a basic understanding of framing, which this article provides.
  • The default partition frame is RANGE, but for aggregate and analytical functions, I find myself using ROWS more often.
  • Remember for tie breaking that the ORDER BY clause should be deterministic if you want consistent results.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *