In this lesson, you will learn how to structure your SQL filter clauses to ensure the database can instantly "seek" to the data you need rather than scanning the entire table.
What is Sargability?
The term Sargable stands for Search ARGument ABLE. When a database designer talks about sargability, they are asking one simple question: "Can the database engine use an index to satisfy this request?"
Think of a database index like the index at the back of a textbook. If you want to find information on "SQL," you look up "SQL" and jump straight to the correct page. That is an Index Seek. However, if you write a query that forces the database to perform a calculation on every single row before checking if it matches, you are essentially forcing the engine to read every page of the book from start to finish. That is an Index Scan, and it is much slower.
The "Hidden" Performance Killer
The most common way developers accidentally destroy sargability is by wrapping a column in a function or performing math on it within the WHERE clause.
Consider a Users table with an index on the CreatedDate column.
Non-Sargable (Bad):
-- The database must calculate YEAR() for every row in the table
SELECT * FROM Users WHERE YEAR(CreatedDate) = 2023;
Because the database doesn't know the result of YEAR(CreatedDate) until it reads the row, it cannot use the index. It must scan every record.
Sargable (Good):
-- The database can look up the range of dates directly in the index
SELECT * FROM Users WHERE CreatedDate >= '2023-01-01' AND CreatedDate < '2024-01-01';
By keeping the column "naked"—meaning it is not inside a function or mathematical operation—you allow the query optimizer to navigate the index tree efficiently. Always aim to perform your transformations on the constant value, not the column itself.
Key Takeaway: To keep your queries fast, never apply functions or calculations to a column in your WHERE clause; instead, adjust your logic so the column stands alone.