SQL SERVER! SARGability: the One Concept You Absolutely Must Understand!

Hi SQL SERVER Guys,

Welcome back to this series about the most important concepts about performance! If you care about SQL Server performance, you cannot miss this post becuase there is one concept you absolutely must understand: SARGability.

In a previous post I showed how dangerous some SQL queries can be for performance:

The Most Dangerous SQL Server Query

Today we look at one of the most important techniques to write faster queries: the SARGable way.

---

What does SARGable mean?

SARGable stands for Search ARGument ABLE.

It means SQL Server can use an index efficiently to find rows.

When a predicate is SARGable SQL Server can perform an:

• Index Seek (fast)

When it is not SARGable SQL Server often performs an:

• Index Scan (slow)

---

A classic NON-SARGable query

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2024;

This query looks simple, but it hides a performance problem.

Because the column is wrapped in the function YEAR(), SQL Server must evaluate the function for every row in the table.

As a result the index on OrderDate cannot be used efficiently.

Execution plan result:

• Index Scan
• High logical reads
• More CPU

---

The SARGable version

SELECT *
FROM Orders
WHERE OrderDate >= '2024-01-01'
AND OrderDate < '2025-01-01';

Now SQL Server can search directly in the index.

Execution plan result:

• Index Seek
• Less IO
• Faster execution

---

Another common mistake

WHERE LEFT(CustomerName,3) = 'Mar'

This is also NOT SARGable.

Better version:

WHERE CustomerName LIKE 'Mar%'

Now the index on CustomerName can be used.

---

Golden rule

When writing predicates remember this rule:

Never manipulate the column in the WHERE clause.

Instead manipulate the value you compare against.

---

Conclusion

SARGability is one of the easiest ways to improve SQL Server performance.

Just a small change in your WHERE clause can transform a slow query into a fast one.

If you want SQL Server to use your indexes properly, always think in the SARGable way.