Sargable-query-in-SQL-server

Indexing of database do magic to improve the performance of the query. But sometime due to bad practice, Index are not used. Sometime we notice that after creating proper index also, SQL server is unable to use Index seek on desired column. Here the Index is ignored.

SARG is an acronym of “Search ARGumentable”. As per wikipedia SARGable is defined as "In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query.

Searches Types: 

Index scan: Scan all Records in a table
Index Seek : use of indexes created by B Tree structure to traverse the tree efficient way to search. To reduce searching . Less than O(n).   log n where base depends upon the branches created in b tree depending upon data.


Non - Sargable Query:  query to not able to harness the benefits  of indexes.

SELECT col FROM table WHERE OrderID *3 = 100

to make it sargeable

SELECT col FROM table WHERE OrderID = 100/3

Non-Sargable:

Hide   Copy Code

SELECT ... WHERE SUBSTRING(FullName,4) = 'Sure'

Sargable:

Hide   Copy Code

SELECT... WHERE FullName LIKE 'sure%'