In another thread, I wrote...
Whenever you have a where clause that performs a function on column data, you will get a table/index scan. This means that each row will have to be evaluated to see if it should be filtered out or included in the results.
When the query engine can use an index, it will identify the records that need to get deleted a lot faster.
For further reading.... SQL Server Sargable
Think of it this way. Suppose someone handed you a dictionary and told you to highlight (with a marker) all words that begin with 'tr'. What would you do? You would flip through the pages (very quickly) and find the first word that starts with TR. You would start highlighting them until you get to the first word that does NOT start with TR.
Now, imagine someone hands you a dictionary and says, highlight each word that contains TR. The only way to do this would be to start at the beginning and examine EVERY word. Obviously this would take a lot longer to accomplish.
By making the query sargable, it will use an existing index to identify the records faster, thus causing the entire operation to take less time. This ONLY works if there is an index.
For example, if someone hands you a 'normal' book and told you to highlight the words that start with TR, you would again have to examine every word. Again... this would take a long time to accomplish.
Make sense?
Brent Ozar also explains it here:
-
George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
