Window Functions for Performance: Practical SQL Examples

Window functions are one of the most useful features in SQL Server when you need to compare, rank, or calculate values across related rows without losing the individual rows.

But first, an important point:

Window functions are not automatically faster.

They often let us replace complicated correlated subqueries, self-joins, or procedural logic with cleaner set-based queries. SQL Server still needs to process the data efficiently, so indexes and execution plans matter.

Let's learn through examples.

1. What Is a Window Function?

The basic syntax is:

FUNCTION() OVER (
    PARTITION BY ...
    ORDER BY ...
)

For example:

SELECT
    CustomerId,
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId
    ) AS CustomerTotal
FROM Orders;

Unlike GROUP BY, every order remains in the result:

CustomerId   Amount   CustomerTotal
-----------  -------  -------------
1            100      600
1            200      600
1            300      600
2            150      400
2            250      400

Think of PARTITION BY as:

"Create a separate window for each group."

2. Example: Latest Record Per Group

A very common requirement:

Get the latest order for every customer.

One approach is a correlated subquery:

SELECT *
FROM Orders o
WHERE OrderDate = (
    SELECT MAX(OrderDate)
    FROM Orders
    WHERE CustomerId = o.CustomerId
);

A window-function approach:

SELECT *
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerId
            ORDER BY OrderDate DESC, OrderId DESC
        ) AS rn
    FROM Orders
) x
WHERE rn = 1;

The idea is simple:

Customer 1
  Order A → 1
  Order B → 2
  Order C → 3

Customer 2
  Order D → 1
  Order E → 2

Then we keep:

WHERE rn = 1

Why add OrderId?

If two orders have the same OrderDate, adding OrderId gives SQL Server a deterministic tie-breaker.

3. Example: Running Total

Suppose we want a running total of sales.

Without window functions, this can require complicated queries.

With SUM() OVER():

SELECT
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        ORDER BY OrderDate
    ) AS RunningTotal
FROM Orders;

Result:

OrderDate    Amount    RunningTotal
----------   -------   ------------
Aug 1        100       100
Aug 2        200       300
Aug 3        150       450
Aug 4        300       750

Want a separate running total for every customer?

SELECT
    CustomerId,
    OrderDate,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate
    ) AS RunningTotal
FROM Orders;

4. Example: Previous Row

Another common requirement:

Compare an order with the customer's previous order.

Use LAG():

SELECT
    CustomerId,
    OrderDate,
    Amount,
    LAG(Amount) OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate
    ) AS PreviousAmount
FROM Orders;

Result:

Customer   Amount   PreviousAmount
--------   ------   --------------
1          100      NULL
1          200      100
1          150      200
2          500      NULL
2          700      500

You can immediately calculate the difference:

SELECT
    CustomerId,
    OrderDate,
    Amount,
    Amount -
        LAG(Amount) OVER (
            PARTITION BY CustomerId
            ORDER BY OrderDate
        ) AS Difference
FROM Orders;

LAG() looks backward.

LEAD() looks forward.

5. Example: Top 3 Per Group

Suppose the requirement is:

Get the three highest-paid employees from every department.

A normal TOP 3 gives three employees overall, not three per department.

Use ROW_NUMBER():

SELECT *
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DepartmentId
            ORDER BY Salary DESC
        ) AS rn
    FROM Employees
) x
WHERE rn <= 3;

The numbering resets for every department:

Department 1 → 1, 2, 3
Department 2 → 1, 2, 3
Department 3 → 1, 2, 3

6. ROW_NUMBER vs RANK

This difference is important.

Suppose salaries are:

ROW_NUMBER():

Salary    RowNumber
------    ---------
10000     1
10000     2
9000      3
8000      4

RANK():

Salary    Rank
------    ----
10000     1
10000     1
9000      3
8000      4

So:

Use ROW_NUMBER() when you need exactly N rows.
Use RANK() when ties should receive the same rank.

7. Where Does Performance Come In?

This is the important part.

Don't assume:

ROW_NUMBER() = Faster

Instead, think:

Better query pattern
        +
Correct indexes
        +
Good execution plan
        =
Better performance

For example, this query:

ROW_NUMBER() OVER (
    PARTITION BY CustomerId
    ORDER BY OrderDate DESC
)

may benefit from an index such as:

CREATE INDEX IX_Orders_Customer_OrderDate
ON Orders(CustomerId, OrderDate DESC);

But the correct index depends on the complete query and workload.

SQL Server may still need operations such as:

Index Seek / Scan
       ↓
Sort
       ↓
Window processing
       ↓
Filter

So always check the actual execution plan, logical reads, and execution time.

8. When Should You Use Window Functions?

They are especially useful when you need to:

Get latest record per group
        ↓
ROW_NUMBER()

Get Top N per group
        ↓
ROW_NUMBER() / RANK()

Calculate running totals
        ↓
SUM() OVER()

Compare previous row
        ↓
LAG()

Compare next row
        ↓
LEAD()

Calculate values across a group
        ↓
SUM() / AVG() OVER()

But don't use them just because they are available.

If you simply need:

SELECT
    CustomerId,
    MAX(OrderDate)
FROM Orders
GROUP BY CustomerId;

then GROUP BY is simpler. There is no reason to introduce a window function.

Final Takeaway

Window functions are not a "make my query faster" button.

Their real strength is allowing you to express problems involving relationships between rows clearly and in a set-based way.

Instead of thinking:

"Which SQL syntax is fastest?"

think:

"What is the simplest correct query, and what does its execution plan look like?"

Then use indexes and measurements to make it faster.

That is where window functions become a powerful tool for SQL performance.