Non-SARGable Queries

As I've been doing some research online, I've come across a term that I am not familiar with.  This term is SARGable and is used in reference to the WHERE clause of select statements.  SARG actually stands for Search ARGument able.  Basically, if you have an index or multiple indexes on a table, a SARGable WHERE clause can take advantage of your indexes.  On the other hand, a non-SARGable WHERE clause cannot take full advantage of the indexes and will cause your query to take longer to run.

Keep in mind that this only applies to WHERE clauses.  If these statement are used in your SELECT statement, it will not affect performance.

So, what makes a query non-SARGable?  Here are some examples:

• Using LIKE with a leading %
• SELECT...... WHERE LastName like '%mith'
• Using a field within a function in the WHERE clause
• SELECT...... WHERE RIGHT(LastName, 5) like 'mith'

Each of these queries skips the index and uses a table scan, causing your query to run longer and resources to be tied up.  So what can we do instead?

• Use =, >, <, >=, <=, BETWEEN, LIKE without a leading %
• SELECT....... WHERE LastName like 'Smith%'
• Use IN, OR, NOT IN, NOT EXISTS, NOT LIKE
• SELECT....... WHERE LastName not in ('Smith')

Using these clues can increase your performance and should be used at all times when possible.

Wikipedia on "SARGable"