Categories
SQL

Cleaning Up Window Functions in SQL Server 2022

Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined.

We’ll use the sample table from the introduction to window functions to look at the new syntax. A typical window function could return year to date sales like this:

SELECT FinancialYear,
	FinancialQuarter,
	SalesYTD = SUM(QuarterlySales)
		OVER (PARTITION BY FinancialYear
			ORDER BY FinancialQuarter)
FROM #QuarterlySales;

Verbose definitions

If we’re adding multiple window functions to a query, they add a lot of text to the definition. In addition to the SUM() function deriving a year-to-date value, let’s do an average quarterly sales year-to-date too:

SELECT FinancialYear,
	FinancialQuarter,
	SalesYTD = SUM(QuarterlySales)
		OVER (PARTITION BY FinancialYear
			ORDER BY FinancialQuarter),
	AvgSalesYTD = AVG(QuarterlySales)
		OVER (PARTITION BY FinancialYear
			ORDER BY FinancialQuarter)
FROM #QuarterlySales;

That’s a lot of extra query for a duplicate window definition. A waste of key strokes for sure. This is where we can simplify with SQL Server 2022.

A new clause

New with SQL Server 2022 is the WINDOW clause which can be added to our queries. This allows us to define the window outside of the SELECT portion of the query.

Using our example above, it can now be rewritten as:

SELECT FinancialYear,
	FinancialQuarter,
	SalesYTD = SUM(QuarterlySales)
		OVER win,
	AvgSalesYTD = AVG(QuarterlySales)
		OVER win
FROM #QuarterlySales
WINDOW win AS (PARTITION BY FinancialYear
	ORDER BY FinancialQuarter);

We declare the window function with a name (win in this case) and then simply refer to it when using the OVER clause on our aggregates. This helps the query to be easier to read and maintain.

And it gives the exact same results.

It’s worth noting that this syntax requires both SQL Server 2022 or higher, plus the database must be at compatibility level 160 (2022) or higher too.

That’s not all it can do. You can define multiple window functions, which can reference each other.

Let’s say we had an additional window function in the select which has similarities in its definition:

	RollingAvgQty = AVG(QuarterlySales)
		OVER (ORDER BY FinancialQuarter
			ROWS BETWEEN 2 PRECEDING
				AND CURRENT ROW)

We’re still ordering the window as we do in the other clause. So we can create that portion of the window and then reuse it. Take a look at this:

SELECT FinancialYear,
	FinancialQuarter,
	SalesYTD = SUM(QuarterlySales)
		OVER ytd,
	AvgSalesYTD = AVG(QuarterlySales)
		OVER ytd,
	RollingAvgQty = AVG(QuarterlySales)
		OVER rolling
FROM #QuarterlySales
WINDOW base AS (ORDER BY FinancialQuarter),
	ytd AS (base PARTITION BY FinancialYear),
	rolling AS (base ROWS BETWEEN 2 PRECEDING
		AND CURRENT ROW);

We’ve taken the ORDER BY portion of the window which is used in both functions, and defined that as a window of its own named base. The window doesn’t need to be used within the query at all. We then use the base window and build on it for the other two – firstly by adding the PARTITION BY clause to create a ytd window, and secondly by setting row bounds for the rolling window.

What you can’t do with this syntax however is add to already defined portion of the window, for example you couldn’t do something like this:

WINDOW base AS (ORDER BY FinancialQuarter),
	reorder AS (base ORDER BY FinancialPeriod)

The ORDER BY is already defined in the base window so can’t be redefined or extended. Referencing can only be used to add additional clauses.

Performance

When comparing the impact of defining the clauses in the SELECT versus the WINDOW clause, I was hoping to see some changes to the query plan or performance.

Alas there was none. But that’s ok. This isn’t a new feature, but new syntax to improve an existing feature. So it’s understandable that how they’re executed hasn’t changed.

Window functions aren’t bad in themselves. Yes they will need sorted data for an ORDER BY clause which can be compounded with multiple functions. If that’s an issue for you, check out a previous post where I demonstrated how you can optimise window functions to remove unnecessary sort operators.

Wrap up

With SQL Server 2022 we have a new option for defining window functions using the new – and imaginatively named – WINDOW clause.

This allows the windows to be defined outside of the SELECT portion of the query. Whilst there are no benefits to be seen in terms of performance, defining the window separately gives other advantages:

  • The same window can be reused across multiple functions
  • Windows can be referenced in the definition of other windows
  • Queries can be easier to read with the lengthy window definitions consolidated

All of these can be useful when we have large complex queries to help support reuse of code. If you’re using multiple window functions within the same query there’s a good chance of some overlap in their definition.

If you’re interested to read more about window functions there are a few previous posts which may be of interest:

2 replies on “Cleaning Up Window Functions in SQL Server 2022”

Leave a reply to The WINDOW Clause in SQL Server 2022 – Curated SQL Cancel reply