MosherData

SARGability (Search ARGument ABLE) is an important concept in SQL Server performance tuning because it allows the database engine to use indexes to quickly retrieve data. When a query is SARGable, it can leverage indexes on the columns, especially in the WHERE clause, enabling SQL Server to directly access the relevant data instead of performing a slower full table scan.

How to Ensure SARGable Queries

Avoid Functions on Columns

Non-SARGable:

SELECT * FROM Users WHERE SUBSTRING(DisplayName, 1, 1) = ‘A’;

This query applies the SUBSTRING function to the DisplayName column, making it non-SARGable.

SARGable:

SELECT * FROM Users WHERE DisplayName LIKE ‘A%’;

This query uses a pattern match that can utilize indexes on the DisplayName column.

Use Equality Comparisons

Non-SARGable:

SELECT * FROM Posts WHERE DATEDIFF(day, CreationDate, GETDATE()) <= 30;

The DATEDIFF function on CreationDate prevents the use of indexes.

SARGable:

SELECT * FROM Posts WHERE CreationDate >= DATEADD(day, -30, GETDATE());

This query uses a direct comparison that allows SQL Server to use indexes on the CreationDate column.

Avoid Negative Conditions

Non-SARGable:

SELECT * FROM Comments WHERE NOT (Score = 0);

The NOT condition makes the query non-SARGable.

SARGable:

SELECT * FROM Comments WHERE Score <> 0;

Using <> instead of NOT can make the query SARGable, allowing the use of indexes on the Score column.

Using OR in WHERE Clauses

Non-SARGable:

SELECT * FROM Users WHERE Location = ‘Canada’ OR Location = ‘United States’;

The OR condition can lead to full table scans.

SARGable (using UNION ALL):

SELECT * FROM Users WHERE Location = ‘Canada’
UNION ALL
SELECT * FROM Users WHERE Location = ‘United States’;

This query uses UNION ALL to split the conditions into separate queries that can each use indexes.

Additional Tips for SARGable Queries

• Use indexed columns in your WHERE clause whenever possible (or index your columns in your WHERE clause).
• Avoid complex expressions and functions on columns in the WHERE clause.
• Consider the data types of the columns and the values being compared to ensure they match.
• Rewrite queries to use range conditions (>=, <=, BETWEEN) instead of functions or non-SARGable expressions.

By writing SARGable queries, you allow SQL Server to use indexes effectively, which can result in faster query execution and reduced resource usage.