Sargable Queries part 2. Examples

Dear Readers,

Today we return to the SARGABLE Queries topic started some days ago.
 
Let's take some examples of Queries that are SARGABLE and Query that are not!

If you missed the first "episode" you still don't know the importance of SARGABLE Queries read my previous article here

Ready?
I hear you say: yes yes..
So let's go! 

Some Examples

Let's create a simple test table and fill it with a bit of data:

       
CREATE TABLE ELENCO
(ID INT IDENTITY(1,1),
 CODICE VARCHAR(3)
CONSTRAINT PK_Elenco_ID PRIMARY KEY CLUSTERED (id)) 
       

The ID field is self-incrementing and is the clustered index.

As a first example, let's write one of the simpler Queries

SELECT ID FROM LIST WHERE ID = 5
       

Let's see the execution plan:

As we would expect the clustered index is used and a SEEK operation is performed.

Now let's change the Query slightly:

SELECT ID FROM LIST WHERE ID + 1 = 5
       

Let's look again at the execution plan.

Who sees the difference?

 

 

Well the difference is noticeable, the clustered index is used but the scan is done (SCAN)

In the first case the Query is SARGABLE, in the second it is not!

 
So be careful, adding even a fixed number to my (numeric) field that  I want to search makes the Query no longer SARGABLE.

KEEP IN MIND: The SQL Server optimizer is not able to transform our Query!

 

 

Second example:

Add a datetime column to our table.
Let's populate it with a bit of data and then put an index on it.

ALTER TABLE LIST ADD START_DATE datetime
UPDATE LIST set START_DATE = CAST (GETDATE () AS int) - ID / 30
CREATE INDEX IDX_ELENCO_START_DATE on LIST (START_DATE)
       

The table will look like this:

Then we write this query:

SELECT START_DATE from LIST WHERE YEAR (START_DATE) = 2019
       

Let's look at the execution plan:

Again a scan is done ... so the YEAR function is not SARGABLE.


Now a question: could we write the SELECT in a better way?
Well yes!

For example:

SELECT START_DATE from LIST WHERE START_DATE> = '2019/01/01' AND START_DATE <= '2019/31/12'
       

And here is the result:

SQL Server this time do an INDEX SEEK!


What makes us understand this example? well two things.

1) We could try to find a way to make SARGABLE a Query that is not
2) Most of the functions are not SARGABLE


As a homework try the same analisys with other functions .... MONTH, DAY, LEFT, SUBSTRING ... etc etc ... Are these sargable or not?

Third and last example: the LIKE operator. 

 

How will it behave?

Let's try writing:

SELECT CODE FROM LIST WHERE CODE LIKE '1%'
       

 

Well the LIKE operator is SARGABLE:

But what if we write this query?

SELECT CODE FROM LIST WHERE CODE LIKE '% 1%'
       

Who guesses?  three.. two.. one..  no one answers?

Then let's see!

No, this time, the query is not SARGABLE.

So the complete answer is therefore IT DEPENDS!



That's all for today too
If you found this article interesting, then keep following me!

See you soon

Luca Biondi @ SQLServerPerformance blog!

Next post: SQL Server and the JOIN operators part 1

Previus post: Write high performing query: Is your query SARGABLE?