You may have heard of the word, SARGable. In brief, it’s a term derived from the phrase, Search ARGument able, and relates to SQL Server’s ability to seek through an index for a predicate. Non-sargable predicates could lead to poor query performance which is why it’s important to understand this. Sargability can be affected with the use of functions in the WHERE, ORDER BY, GROUP BY and HAVING clauses.

There are a number of articles that talk about sargability but we are going to focus just on the ISNULL function and it’s effect on sargability in this article. There is something quite interesting in store too. I will be running some queries against the StackOverflow database in order to present this.

Let’s start by running the following query

SELECT TOP 10 Id 
FROM Users
WHERE Age > 18

Before looking at the execution plan, I think it’s important to know the schema and the index available.

The execution plan looks as follows, just as you would expect.

Now let’s run the following query

SELECT TOP 10 Id 
FROM Users
WHERE ISNULL(Age, 0) > 18

Note the ISNULL function on the column ‘Age’ in the WHERE clause.

Let’s look at the execution plan as well.

We are now doing a scan on the non-clustered index instead of a seek, because SQL does not think the predicate is sargable any more. You might think “Why the non-clustered index?” Since the non-clustered index is the smallest index available to SQL Server that provides all the column it requires to run the query, it does not need to scan the clustered index.

So let’s experiment a bit here on.

So how many NULL values do I have in the Age column?

SELECT COUNT(*) 
FROM Users 
WHERE Age IS NULL

I get 3053086.

And what do my statistics look like:

DBCC SHOW_STATISTICS('[dbo].[Users]', [IX_Users_Age])

So could it be from the statistics that SQL knows we have NULL values and hence it doing a scan on the non-clustered index?

Let’s update all the NULL values and replace them with ‘0’ instead.

UPDATE Users
SET Age = 0 
WHERE Age IS NULL

Let’s also make sure that the statistics are updated.

Let’s re-run our query again and check out the execution plan.

No change as you can see above.

I wonder if it’s the column definition – the column Age accepts NULL values.

Let’s change this column definition. To do this we would need to drop the index on Age and re-create it.

/****** Object:  Index [IX_Users_Age]     ******/DROP INDEX [IX_Users_Age] ON [dbo].[Users]
GO
ALTER TABLE Users
ALTER COLUMN Age int not null
GO
/****** Object:  Index [IX_Users_Age]     ******/CREATE NONCLUSTERED INDEX [IX_Users_Age] ON [dbo].[Users]
(
[Age] ASC
)

Time to re-run our query.

Voila!

Back to seek… This is somewhat an “educated” behaviour from SQL Server.

TOP TIP: As mentioned right at the beginning, non-sargable queries will impact your query performance adversely. There might be scenarios which might lead you to think that it’s not true. However, in order to prevent your query from suffering this degradation in performance it’s best to keep your predicates sargable and avoid using functions in the WHERE, GROUP BY, ORDER BY and HAVING clauses.

Another important point to note is that if you didn’t have a suitable non-clustered index and SQL Server was scanning the clustered index because of a function on the predicate, you wouldn’t get any missing index details from the DMVs (Dynamic Management Views) to help you improve query performance.

The post ISNULL around the predicate and SARGability appeared first on SQLYSE.