Introduction to new the SELECT WINDOW Clause in SQL Server

 

     WINDOW Operations in SQL Server can be hard to understand.  I believe one of the main reasons for that is the long and repeated code it needs. SELECT...WINDOW Clause will help us to remove repeated code and hopefully it will make the WINDOW Operations more user-friendly.

    Let's look at the following example first. It looks like a lot going on when you look at it the first time. Then you realize there is a lot of repeated code in it. 

SELECT DISTINCT i.AccountsPersonId,
TotalInvoices = COUNT(i.InvoiceId) 
OVER (PARTITION BY i.AccountsPersonId ORDER BY i.AccountsPersonId),
TotalProfit = SUM(l.LineProfit) 
OVER (PARTITION BY i.AccountsPersonId ORDER BY i.AccountsPersonId),
AvgQuantity = AVG(l.Quantity)  
OVER (PARTITION BY i.AccountsPersonId ORDER BY i.AccountsPersonId),
TotalDryItems = SUM(i.TotalDryItems) 
OVER (PARTITION BY i.AccountsPersonId ORDER by i.AccountsPersonId),
TotalChillerItems = SUM(i.TotalChillerItems) 
OVER (PARTITION BY i.AccountsPersonId ORDER by i.AccountsPersonId)
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS l ON i.InvoiceId = l.InvoiceId
WHERE invoicedate BETWEEN '2013-1-1' and '2014-1-1'
    

  To fix this problem, the SQL Server team came up with a great solution to remove all the repeated code and make the code cleaner. You can define the PARTITION BY and ORDER BY in the new WINDOW clause and use it wherever you like in your query. Before you try the new clause in your database, you want to be sure that the database has the Compatability 160 or higher version. You can change the compatibility level of a database with the following command.

    ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160    

     Now, we are ready to rewrite the code with the new WINDOW clause. In the following example, I define the WINDOW at the end of the query and use the defined window in the select statement.

SELECT DISTINCT i.AccountsPersonId,
COUNT(i.InvoiceId) OVER accpidWin AS TotalInvoices,
SUM(l.LineProfit) OVER accpidWin AS TotalProfit,
AVG(l.Quantity)  OVER accpidWin AS AvgQuantity,
SUM(i.TotalDryItems) OVER accpidWin AS TotalDryItems,
SUM(i.TotalChillerItems) OVER accpidWin AS TotalChillerItems
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS l ON i.InvoiceId = l.InvoiceId
WHERE invoicedate BETWEEN '2013-1-1' and '2014-1-1'
WINDOW accpidWin AS (PARTITION BY i.AccountsPersonId ORDER BY i.AccountsPersonId)    

     New syntax helps to organize the code and makes it clean. This query returns the following results.