Jack Corbett (7/31/2009)
Matt,The only problem with LTRIM(RTRIM(column)) in comparison (WHERE or JOIN) clauses it that you no longer give the optimizer the option to use an index seek, the best it can do it scan as it HAS to evaluate every row using the function. And, as the chart shows, for equality/inequality that is unnecessary.
Certainly using it when inserting/updating a value is okay, although, in my opinion, the UI/business layer should clean this up.
Fully agree! I have to issue these warnings to all entusiastic developers who rush into using functions and "clever" UDFs and end up peppering the WHERE clause with such stuff that almost kills the server...
And Thank You Jack once again for taking the trouble and being so thorough.