Database developers usually know about aggregate functions, but very few know usage of Window functions in SQL Server, these operate on a set of rows and return a single aggregated value for each row, so in this article, I have mentioned what is window function in sql server and how to use window function in SQL Server with example.

In Window function, term "Window" describes the set of rows in the database on which the function will operate. Window Functions were introduced in SQL Server 2005 but with SQL Server 2012, we can enjoy full range of Window Functions. 

Since SQL Server 2005 we support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.

But from 2012 or above SQL Server, we have 3 type of Windows Functions:

  • Aggregate Window Functions: SUM(), MAX(), MIN(), AVG(). COUNT()
  • Ranking Window Functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
  • Value Window Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Window functions all use the OVER() clause, which is used to define how the function is evaluated. The OVER() clause accepts three different arguments:

  • PARTITION BY: Resets its counter every time the stated column(s) changes values.
  • ORDER BY: Orders the rows the function will evaluate. This does not order the entire result set, only the way the function proceeds through the rows.
  • ROWS BETWEEN: Specifies how to further limit the rows evaluated by the function.

We will use Order By as arguments in our examples.

So basic syntax of Window function in SQL Server is as below

window_function ( [ ALL ] expression ) 
OVER ( [ PARTITION BY partitionList ] [ ORDER BY orderList] )

So let's create a sample database table with sample values

 CREATE TABLE [dbo].[Order]
(
	Id INT,
	OrderDate DATE,
	CustomerName VARCHAR(250),
	City VARCHAR(100),	
	OrderAmount MONEY
)
 
INSERT INTO [dbo].[Order]
SELECT '1001','04/01/2022','John Wick','New York',10000
UNION ALL	  
SELECT '1002','04/02/2022','David Yates','Chicago',20000
UNION ALL	  
SELECT '1003','04/03/2022','John Wick','New York',5000
UNION ALL	  
SELECT '1004','04/04/2022','Michael Smith','Chicago',15000
UNION ALL	  
SELECT '1005','04/05/2022','David Warner','Chicago',7000
UNION ALL	  
SELECT '1006','04/06/2022','Paul Smith','Chicago',25000
UNION ALL	 
SELECT '1007','04/10/2022','Andrew Smith','Arlington',15000
UNION ALL	  
SELECT '1008','04/11/2022','David Brown','Arlington',2000
UNION ALL	  
SELECT '1009','04/20/2022','Rupert Smith','New York',1000
UNION ALL	  
SELECT '1010','04/25/2022','Palmer Smith','Chicago',500

Now suppose we want to get sum of each city, then using Aggreate function SUM() we can get it.

SELECT City, SUM(OrderAmount) total_order_amount
FROM [dbo].[Order] GROUP BY City

This will return output as below

City	        total_order_amount
-----------------------------------
Arlington	17000.00
Chicago	        67500.00
New York	16000.00

But as you can see from above output, rows returned are grouped value of each city.

This does not happen with window aggregate functions. Rows retain their identity and also show an aggregated value for each row.

So if we use window functions for above example query, it would be as below

SELECT Id, OrderDate, CustomerName, City, OrderAmount
 ,SUM(OrderAmount) OVER(PARTITION BY City) as GrandTotal 
FROM [dbo].[Order]

and the output of the above query will be as below

window-functions-in-sql-server-example

As you can see from output, query now, aggregates the data for each city and shows the sum of total order amount for each of them.

Similary, we can use it with Rank() function.

Rank() function is used to give rank to each row based on the specified value and if the value of 2 rows are same, we give it same rank.

So, if we use Rank() by order Amount with Window function, query would be as below

SELECT Id,OrderDate,CustomerName,City, 
RANK() OVER(ORDER BY OrderAmount DESC) [Rank]
FROM [dbo].[Order]

and output of the above query will be:

window-functions-in-sql-server

Similarly, you can use it in various other aggregate functions and make quering data more useful.

You may also like to read:

SQL Comments (Comment in SQL Query)

Find nth highest salary in SQL Server

How to backup SQL Server database ? (Various Ways explained)

How to Select LAST N Row in SQL Server

GO Statement in sql server

Extract String after a character or before character in SQL Server

Convert Int To String (varchar) in SQL Server

If-Else in SQL Server with Example

Find Duplicate Rows in SQL Server

Import an SQL file in MySQL (Using CMD or Powershell)