SQL Server Portal

SQL Server 2022 brought some exiting features which will help us in optimising the SQL scripts. Window clause is one of the new features. Also, it helps us to reduce the code complexity when we deal with multiple OVER Clause.

As we can see in the below example that in the OVER Clause, we defined same Partition By & Order By, over and over again, since there was no Window clause available in the earlier edition of SQL Server.

USE Northwind
GO

SELECT [OrderID]
     , [ProductID]
	 , ROW_NUMBER()     OVER (PARTITION BY [ProductID] 
	                          ORDER BY     [ProductID]) AS [RowNo]
	 , [UnitPrice]
	 , [Quantity]
     , AVG([UnitPrice]) OVER (PARTITION BY [ProductID] 
	                          ORDER BY     [ProductID]) AS [AverageUnitPricePerProduct]
	 , SUM([Quantity])  OVER (PARTITION BY [ProductID] 
	                          ORDER BY     [ProductID]) AS [TotalQuantityPerProduct]
FROM   [dbo].[Order Details]
WHERE  [ProductID] IN (9,15);
GO
--OUTPUT

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use Window clause. Given below is the script to change the database compatibility level.

 ALTER DATABASE Northwind SET COMPATIBILITY_LEVEL = 160 

In the new approach, you can simply define a Window clause just one time and use it over and over again in your query as shown below. It gives us the same result as mentioned above but with less complexity.

USE Northwind
GO

SELECT [OrderID]
     , [ProductID]
	 , ROW_NUMBER()     OVER Window1 AS [RowNo]
	 , [UnitPrice]
	 , [Quantity]
     , AVG([UnitPrice]) OVER Window1 AS [AverageUnitPricePerProduct]
	 , SUM([Quantity])  OVER Window1 AS [TotalQuantityPerProduct]
FROM [dbo].[Order Details]
WHERE [ProductID] IN (9,15)
WINDOW Window1 AS (PARTITION BY [ProductID] 
                   ORDER BY     [ProductID]);
GO
--OUTPUT

Conclusion:

The Window clause is very handy when we have the same Window clause used by multiple aggregate functions. It also reduces the complexity. Do let me know if you used Window clause and found it useful or not.

Read Full Post »