SARGability

A query is SARGable (Search ARGument Able) if the database engine can take advantage of an index to speed up the execution of the query. If you write a query in a way that “hides” the data from the index, the database is forced to perform a slow Table Scan.

Definition: The SARGable Rule

To be SARGable, a predicate (your WHERE clause) must not perform any calculation or function on the column itself. The column must stand alone on one side of the operator.

Context: The Phone Book Analogy

Imagine a phone book sorted by “Last Name.”

SARGable: “Find everyone whose last name is ‘Smith’.” (You jump straight to ‘S’).
Non-SARGable: “Find everyone whose last name starts with ‘S’.” (This is still SARGable).
Non-SARGable: “Find everyone whose last name ends with ‘h’.” (You have to read every single name in the book).

Detailed Explanation: Common SARGability Killers

1. Functions on Columns

Non-SARGable (Slow)

WHERE UPPER(Email) = 'ALICE@EXAMPLE.COM'

SARGable (Fast)

WHERE Email = 'alice@example.com'

In the slow version, the database must run the UPPER function on every single row before it can compare it.

2. Date Calculations

Non-SARGable (Slow)

WHERE YEAR(OrderDate) = 2023

SARGable (Fast)

WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'

3. Leading Wildcards

Non-SARGable (Slow)

WHERE Name LIKE '%Smith'

SARGable (Fast)

WHERE Name LIKE 'Smith%'

4. Mathematical Operations

Non-SARGable (Slow)

WHERE Salary * 1.1 > 50000

SARGable (Fast)

WHERE Salary > 50000 / 1.1

Example: Real-World Performance Hit

We have a Users table with 10 million rows and an index on CreatedAt.

Query A (Non-SARGable)

SELECT COUNT(*) FROM Users WHERE DATE(CreatedAt) = '2023-01-01';

Execution Time: 5 seconds (Table Scan).

Query B (SARGable)

SELECT COUNT(*) FROM Users
WHERE CreatedAt >= '2023-01-01 00:00:00'
AND CreatedAt < '2023-01-02 00:00:00';

Execution Time: 0.002 seconds (Index Seek).

Under the Hood: The Index Leaf

Why does a function kill an index?

A B-Tree index stores the original values of the column in sorted order. If you ask for UPPER(Name), the index doesn’t contain those uppercase values; it only contains the original ones. The database has no way to “look up” a transformed value in a sorted list of un-transformed values.

The Exception: Expression-Based Indexes

Some advanced databases (Postgres, Oracle) allow you to create an index on a function: CREATE INDEX idx_upper_name ON Users(UPPER(Name)); In this specific case, the UPPER(Name) query becomes SARGable because the index itself stores the transformed values.