Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Window Functions in Batch Mode
Published: Dec 03, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.