A Guide to Sargable Queries
Tags: sql, index, sargable, query
Published: 2023-12-22 19:47:45

## Overview
In the vast realm of relational databases, query performance is a critical factor that can make or break the efficiency of applications. One essential concept in the pursuit of optimized queries is "sargability." Sargable, short for "Search ARGument ABLE," refers to the ability of a query to make efficient use of indexes, ultimately leading to faster and more scalable database operations.

## Understanding Sargability
Sargability revolves around the idea of crafting queries in a way that allows the database engine to leverage indexes effectively. The key lies in constructing queries that can take advantage of index structures, enabling the database to quickly locate and retrieve relevant data. Let's delve into the characteristics that define sargable queries.

## Direct Column References
Sargable queries often involve direct column references in the _WHERE_ clause. When a query directly references a column without applying functions or manipulations, the database engine can efficiently utilize indexes associated with that column. For instance:

`SELECT * FROM Employees WHERE Salary > 50000;`

In this example, the query is sargable because it directly references the _Salary_ column.

## Index-Friendly Functions
While using functions in queries is common, not all functions are **index-friendly**. Sargable queries use functions that still allow the database engine to take advantage of indexes. Consider the following example:

`SELECT * FROM Orders WHERE DATE(OrderDate) = '2023-01-01';`

The sargability of this query depends on the database's ability to optimize the use of an index on the _OrderDate_ column despite the presence of the _DATE_ function.

## Avoiding Function on Columns
To maintain sargability, it's advisable to avoid applying functions directly to columns in the _WHERE_ clause whenever possible. Instead of using:

`SELECT * FROM Customers WHERE UPPER(CustomerName) = 'ABC COMPANY';`

Consider:

`SELECT * FROM Customers WHERE CustomerName = 'ABC Company';`
This adjustment enhances the likelihood of the query being sargable.

## Range Queries
Sargable queries often involve range conditions, which can efficiently use indexes. For instance:

`SELECT * FROM Products WHERE Price BETWEEN 10 AND 50;`

This type of query can leverage an index on the _Price_ column for optimal performance.

## Conjunctions of Sargable Expressions
Combining multiple sargable expressions using AND typically results in a sargable query. Consider the following:

`SELECT * FROM Employees WHERE Salary > 50000 AND DepartmentID = 3;`

This query efficiently uses indexes on both the _Salary_ and _DepartmentID_ columns.

## Complex Expressions
Queries with complex expressions that involve multiple operations or calculations may not be sargable. For instance:
`SELECT * FROM Sales WHERE Quantity * Price > 1000;`

The multiplication of _Quantity_ and _Price_ might prevent efficient index usage.

## Using LIKE Pattern
Queries using _LIKE_ patterns with wildcards at the beginning **(%prefix)** can be non-sargable, as they require scanning the entire column. For example:

`SELECT * FROM Customers WHERE CustomerName LIKE '%Corp';`

This query might not efficiently use an index on _CustomerName_.

## Conclusion
Sargability is not just a theoretical concept; it directly influences the speed and responsiveness of applications. **The practice of using direct column references, employing index-friendly functions, and steering clear of unnecessary manipulations on columns enables databases to harness the power of indexes effectively**. This, in turn, translates to faster data retrieval and enhanced overall system performance.