Sargability... September, 2006
Recently I was looking up some information on enhancing query performance and came accross a new term; Sargable. A "contraction of Search ARGument" according the wikipedia. Basically sargability means that the DB server can take advantage of an index to speed up the query. WHERE clauses that arent sargable can cause a table scan because every value, in certain referenced column(s), in the entire table must be evaluated. One example would be MyValue=LTRIM(SomeTable.SomeValue). The result of the LTRIM is not indexed, therefore every value, in the SomeValue column, in the SomeTable table must be evaluated. If possible, you can rework a clause so that DB server can take advantage of an index. For example, instead of saying Year(ThisDate)=Year(GETDATE()) you could say ThisDate BETWEEN YearBegin(GETDATE()) AND YearEnd(GETDATE()). Where YearBegin and YearEnd are deterministic functions that return the first and last day of the year respectively. Or instead of saying WHERE DATEDIFF(day, SomeDateField, GETDATE()) > 7 you could say WHERE SomeDateField < DATEADD(day, -7, GETDATE()). In the first statement, every record must be evaluated in order to see if the condition is true. But in the second statement the current date is processed with the DATEADD function to give is a max date and the evaluation is done with this constant result (Since the DATEADD on GETDATE will only be run once when the query starts) which could then take advantage of an index. If the clause cannot be reworked to be sargable then you could create an indexed computed column.