PiMané (10/3/2012)
Sean Lange (10/3/2012)
NO LIKE is not SARGable so you will get scans. It has to examine every row to determine if it is a match or not.So a LIKE 'M%' is better replaced with a >= 'M' AND < 'N'..
No, that is wrong! LIKE 'M%' is SARGable, you don't need to replace it with such a mess
However, LIKE '%M' is not SARGable!. But replacing it with comparison operators will be quite problematic :-D.
If you really need the best possible performance for text searches like the above, there is SQL Server feature called Full Text Search. http://msdn.microsoft.com/en-us/library/ms142571.aspx
It's designed for performing effective comprehensive text searches.