SARGable stands for "Search ARGument-able." A query is SARGable if the SQL Server engine can use an index to speed up the search. Many developers write "Non-SARGable" queries that force a full table scan, even if an index exist.
If you wrap a column in a function in the WHERE clause, the index is wasted. SQL Server cannot "see" inside the function to use the sorted index.
-- NON-SARGable (BAD)
SELECT * FROM Users WHERE YEAR(CreatedDate) = 2024
-- SARGable (GOOD)
SELECT * FROM Users WHERE CreatedDate >= '2024-01-01' AND CreatedDate < '2025-01-01'Searching for LIKE '%Sandeep' is NOT SARGable because the index is sorted by the start of the string. Searching for LIKE 'Sandeep%' IS SARGable because SQL can jump directly to the 'S' section of the index.
Q: "How can I find non-SARGable queries in my production environment?"
Architect Answer: "You look at the **Execution Plan** for an 'Index Scan' or 'Table Scan' matched with a 'Predicate' that involves a function. You can also use Extended Events or Query Store to look for queries with high CPU but low logical reads, which often indicates that SQL is doing massive calculations on every row instead of using an index."