Improving performance by using sargable conditions

Sargable stands for Search ARGument Able. Sargable conditions help query optimizers to use the index defined on column(s) effectively. Sargable conditions have a higher chance of meeting index seek than index or table scan.

Getting ready

Writing a sargable condition is also a preventive step. Consider this factor while writing the query or while working on performance tuning projects. There are some operators that make your query sargable or non-sargable. Here is the list:

Sargable operators:

  • =
  • >
  • >=
  • <
  • <=
  • BETWEEN
  • LIKE (only those LIKE conditions that have a wildcard character as a suffix, for example, FirstName LIKE 'R%')

Non-sargable operators:

  • !=
  • !<
  • !>
  • <>
  • NOT EXISTS
  • IN
  • NOT IN
  • LIKE (LIKE conditions that have ...

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial