We all love Window Functions. Where would we be without ROW_NUMBER and RANK? But these can sometimes be performance killers as they loop through our data performing sorts and scans as they go.
Now, in SQL Server 2016+, there is a Batch Mode version of Window Functions and the improvement is huge.
So let’s have a look with a couple of quick examples:
First we’re going to use a traditional rowstore index…
drop table if exists salesOrderHeader
go
select *
into salesOrderHeader
from AdventureWorks2012.sales.SalesOrderHeaderEnlarged
create clustered index cix_salesOrderHeader
on SalesOrderHeader(orderDate)
select *
from
(
select row_number() over(partition by salesPersonID order by orderDate) id, *
from SalesOrderHeader
) x
where id = 1
go
Secondly we’ll use a Columnstore index in SQL Server 2014…
create clustered columnstore index cix_salesOrderHeader
on SalesOrderHeader with (drop_existing = on)
select *
from
(
select row_number() over(partition by salesPersonID order by orderDate) id, *
from SalesOrderHeader
) x
where id = 1
go
Now, the exact same thing but in SQL Server 2017 with Batch Mode Window Functions:
As you can see, the results are dramatic.
Rowstore and Columnstore are very similar in performance (the Columnstore lost slightly due to higher CPU and Reads)… but the Batch Mode Window Function was a massive improvement. The Duration was reduced by nearly 75%.
Therefore we no longer have a reason to shy away from using our favourite functions anymore. Batch mode, once again, solves many of our problems.