Sake Of
SARGability is the in-club way of saying that a search predicate(s) can be used to seek through the key(s) of an index.
Some things that mess it up are:
- function(column) = something
- column + column = something
- column + value = something
- value + column = something
- column = @something or @something IS NULL
- column like ‘%something’
- column = case when …
- value = case when column…
- Mismatching data types
Yes, this has all been written about quite a bit — here and elsewhere — but it’s a query pattern that I still spend a lot of time fixing.
So here we are. More writing about it.
If you’re sick of hearing about it, stop doing it.
Symptomatic
So let’s say we’ve got this table:
CREATE TABLE
dbo.sargability
(
id int PRIMARY KEY,
start_date date,
end_date date
);
Right now, the only index on this table is on the id column. Since it’s the clustered index (by default, since it’s the primary key), it also “includes” the start_date and end_date columns. Conversely, this also means that any nonclustered indexes we create will have the id column automatically added to them. In the case of a non-unique index, it will be in the key. In the case of a unique index, it will be in the “includes”.
I once had someone go back and forth with me quite a bit about that last point, insisting that the clustered index didn’t have all of the table’s columns in it. But you know, if we run this query, we only touch the clustered index:
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s;
Slippery
This means two things: as long as we avoid the lapses in judgement listed up above, we can seek to a single value or range of values in the id column. The index has put the data in order (in this case ascending).
It also means that start_date and end_date are not in a searchable order, so any query we write that attempts to search/filter values there will have to scan the index (unless we also search/filter the id column).
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s
WHERE id = 1;
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s
WHERE s.start_date = '20210808';
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s
WHERE s.end_date = '20210808';
Recreation
Even though the equality predicates on start_date and end_date are perfectly SARGable, there’s no index for them to use to seek to those values in. They’re only in the clustered index, which is ordered by the id column. Other columns are not in a helpful order.
The fact that both of these queries end up scanning the clustered index may leave you under the impression that the isnull version is an acceptable practice.
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s
WHERE s.end_date = '20210808';
SELECT
s.start_date,
s.end_date
FROM dbo.sargability AS s
WHERE ISNULL(s.end_date, '19000101') = '20210808';
But with an index on the column the problem becomes more apparent, with the “good” query seeking and the “bad” query scanning.
CREATE INDEX s ON dbo.sargability(end_date) INCLUDE(start_date);
Evidence
For the rest of the week, we’re going to look at various ways to fix non-SARGable queries with things like computed columns, temp tables, index key column order, and dynamic SQL.
These are the approaches I normally take in my query tuning work, so hopefully others will find them helpful.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
