SQL Server 2022 and windowing improvements. Learn SQL with me!

Hi guys,

In the last four posts we talked about the T-SQL news that SQL Server 2022 brought us.

• SQL Server 2022 and the DATE_BUCKET function. Work more easily with dates 
• SQL Server 2022 and the GREATEST / LEAST T-SQL commands 
• SQL Server 2022 and the STRING_SPLIT command .. string splitting made easy! 
• SQL Server 2022 and the GENERATE-SERIES command .. a T-SQL language enhancement 
  

Today we will talk about another T-SQL extension. 

We will talk about the Window clause in the window functions.

As always, I will try to be as clear as possible!

Enjoy this post!

 

The Window Clause

With SQL Server 2022, Microsoft introduce in the window functions the concept of window.

For the sake of truth this is not a feature invented by Microsoft because this concept is part of the ISO/IEC SQL standard.

If you doesn't know what a window function is, you can read here: Learn SQL Server Window functions and Running Totals in five minutes 

Looking at the example below, the part highlighted in yellow of the window function is called window.

SELECT  
  SUM(qty) OVER( PARTITION BY IdOrdTes
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING )
FROM OrdRig
ORDER BY id, IdOrdTes;

In SQL Server 2022 the window becomes "a concept" and we can define it after the WHERE clause:

WINDOW W AS ( PARTITION BY IdOrdTes
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING ) 

In this way we can white the first T-SQL command as:

SELECT  
  SUM(qty) OVER W  
FROM OrdRig
WINDOW W AS ( PARTITION BY IdOrdTes
              ORDER BY id
              ROWS UNBOUNDED PRECEDING )
ORDER BY id, IdOrdTes;

We will see in the example below that this allows you to write the text more compactly

 

Example

Let's create an example using our usual ORDRIG table.

Create Table OrdRig (
  id int identity(1,1),
  IdOrdTes int,
  qty float,
  val float
)

This table contain the rows of an order.

Let's populate the table with some data:

Insert into OrdRig (IdOrdTes,qty,val) values (1,5,1)
Insert into OrdRig (IdOrdTes,qty,val) values (1,3,1)
Insert into OrdRig (IdOrdTes,qty,val) values (1,2,1)
Insert into OrdRig (IdOrdTes,qty,val) values (2,2,1)
Insert into OrdRig (IdOrdTes,qty,val) values (2,2,1)
Insert into OrdRig (IdOrdTes,qty,val) values (2,2,1)
    

 

Now we want to show so called running totals for both the columns Qty and Val

Running totals must by partitioned by the field IdOrder; the value must re-start from 0 when the value of the IdOrder field change.

I have wrote this:

SELECT 
  IdOrdTes, qty, val,

  SUM(qty) OVER( PARTITION BY IdOrdTes
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING ) AS running_tot_Qty,

  SUM(val) OVER( PARTITION BY IdOrdTes
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING ) AS running_tot_Val

FROM OrdRig
ORDER BY id, IdOrdTes;

What I get is:

 

The concept is that I have a "window" that contains (is wide) as many records as there are rows in the table with the same idOrdTes.

While I am on a row of the table i can see data of all the record that are inside my window.

The heart of the running totals is the clause Row Unbounded preceding : for each row you are located the function outside the OVER clause (the SUM in this example) will consider the rows from the beginning of the window until the row you are located.

Numerous other clauses are present

 

With SQL Server 2022 we can define a window and give to it an alias (W)

WINDOW W AS ( PARTITION BY IdOrdTes
                 ORDER BY id
                 ROWS UNBOUNDED PRECEDING ) 

Now we can write the same statement used to get the running totals in this ways:

SELECT 
  IdOrdTes, qty, val,

  SUM(qty) OVER W AS running_tot_Qty,
  SUM(val) OVER W AS running_tot_Val

FROM OrdRig

WINDOW W AS ( PARTITION BY IdOrdTes
              ORDER BY id
              ROWS UNBOUNDED PRECEDING )

ORDER BY id, IdOrdTes;

In this way the code is shorter and we can see clearly that both field are using the same WINDOW.

Obviously the results are the same.

other details...

We can define more than one window ...or we can play a little, using window in a recoursive way.

 

For example we can write:

WINDOW P AS ( PARTITION BY custid ),
       PO AS ( P ORDER BY orderdate, orderid ),
       POF AS ( PO ROWS UNBOUNDED PRECEDING )            

In this case we have defined 3 WINDOWS where one window is contained in another ..

The PO window is the P window but it is order

The POF window is the PO windows but it has also the ROWS UNBOUNDED PRECEDING clause.

It is now easy for example write this more complex command.

 

SELECT 
  ROW_NUMBER() OVER PO AS ordernum,
  MAX(Qry) OVER P AS max_Qty,
  SUM(qty) OVER POF AS running_tot_Qty,
  SUM(val) OVER POF AS running_tot_Val
FROM OrdRig
WINDOW P AS ( PARTITION BY IdOrdTes ),
       PO AS ( P ORDER BY Id),
       POF AS ( PO ROWS UNBOUNDED PRECEDING )  
ORDER BY custid, orderdate, orderid;

 

That's all for today!

I hope you enjoyed this post.

 

If you find an advertisement that genuinely interests you, simply click it from on my blog. 

You will help me keep writing more and more interesting content. ~Luke

Previous post: SQL Server 2022 and the DATE_BUCKET function. Work more easily with dates