Working with DATES in a SARGABLE way ...and gofast! ...All clearly explained!

Hi Guys,

Welcome to this new post!
We have already spoken many times about sargability.

For example here:

Sargability is the key factor to having a performant query but talking about dates is not always easy to get this property.

Today we will see how to turn a non sargable query using dates into a sargable one.

Obviously the difference in performance is really remarkable!

Enjoy!

Working with dates in a sargable way

Sargability is a great property!

We can say that a query is sargable when its WHERE clause can use index in seek mode.

I suggest you read my other posts where I tell you why often the queries are not sargable.
For example when I use a function, but there are other cases..

And often when we handle dates we use functions.
Who has never used the datepart function?
Well, using this feature makes the query not-sargable!

So let’s see how to write a sargable query.
For example, suppose you have a table called OrdTes that contains a datetime field DateOrd like the date of when an invoice was inserted.
Suppose you want to extract all invoices issued since last year..

We could write the following query:


SELECT DATEORD FROM ORDTES WHERE DATEPART(yy,DATEORD) >= DATEPART(yy,GETDATE()) -1

We get the start of the current year by using the DATEPART function with the paramter yy (year)
Now to get the beginning of last year we subtract 1

Cool ...but we have a problem: the DATEPART function of a field of a table is not sargable!

How can we then write our Query in such a way that it is sargable?

Well you must know that we can use the two function DATEADD and DATEDIFF.

The "hint" is to write the WHERE clause of the Query without any function applied to the DATEORD field.


SELECT DATEORD FROM ORDTES WHERE DATEORD >= ...

Yes but how to write the part of the query at the right of the equal symbol using only the DATEDIFF and DATEADD function?

I do it this way?


SELECT DATEORD FROM ORDTES WHERE DATEORD) >= DATEADD( yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()), 0))

I do these 3 steps:

First with the DATEDIFF(yy,0,GETDATE)) function returns the number of the years from the 1900 and so the value 122.
Then with the DATEADD(yy, ... ) function i get the start of the current year and so the date 01/01/2022
Finally i find the start of the last year with the DATEADD (yy, -1 ...) function called with the first parameter equal to yy (year) and the second parameter equal to -1 (i substract one year)

Le'ts now compare the two solutions with a benchmark.

The Benchmark!

You can repeat the test using the following script that create the OrdTes table and then fill it with some data:


Create table Ordtes (id integer identity(1,1), DateOrd datetime primary key (id))
Create index idx_Ordtes_DateOrd on Ordtes(Dateord)
 
Declare @id integer = 0
While @id < 27656193   
begin
  Insert into OrdTes (Dateord)
    Select dateadd(dd, -@id ,dateadd(yy,0,getdate()))

  set  @id = @id + 1
end

Now we can compare the two following query:


Select DaTeOrd from OrdTes where DATEPART(yy,DateOrd) >= DATEPART(yy,GETDATE()) -1

Select DateOrd from OrdTes where DateOrd >=  DATEADD( yy, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))