The SQL Server WINDOW clause is a new feature introduced in Microsoft SQL Server 2022. It can make the syntax of a window query much more readable.
In this brief tutorial, we’ll walk through how the WINDOW clause works and see how it can be very helpful.
Start with a normal window query
Let’s start with a very simple query that uses the RANK() window function. We’ll run the query against a Sales table which contains the following content:
Let’s say we wanted to establish a ranking per product of the sale dates. For example, it looks like we have sold our product #45 three times, on the following dates:
- 3/9/2024
- 3/11/2024
- 3/25/2024
We’d like to establish a ranking of these dates. For example, the first sale of product #45 on 3/9/2024 will have a rank of 1. The next sale on 3/11/2024 will have a rank of 2. The next on 3/25/2024 will have a rank of 3. We’re basically ranking each sale according to its date. And again, we want to do this for each product.
Here’s what the query would look like:
If you need to know more about the RANK() function (and other ranking window functions), check out the following tutorial:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
We can use the WINDOW clause to make the query easier to read
We can introduce the WINDOW clause to make our query a bit easier to read. Here is what the query looks like, then we’ll talk about it:
What we do is basically push the content of the OVER clause to the WINDOW clause. In the WINDOW clause, we name that content. Then in the OVER clause, all we need to do is reference that name!
Instead of having that window definition in the column list, where it can be difficult to read and understand, we just push all that syntax to its own dedicated clause that defines everything. This can make the query easier to understand!
So simple!
Next Steps:
Leave a comment if you found this tutorial helpful!
In you need a crash course on window functions, check out the following beginner-friendly tutorial:
SQL Server Window Functions: An introduction for beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
