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.
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.
to make it sargeable
Sargable:
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 tableIndex 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%'
