>SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses

>

Just creating indexes on JOIN, WHERE and GROUP clause columns doesn’t mean that your query will always return your required results quickly. It is query optimizer which selects proper index for a query to give you an optimum performance but query optimizer can only suggest optimum query plan by using proper indexes WHEN your are helping it by writing good query syntax.

Using any type of function (system or user defined) in WHERE or JOIN clause can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. One common example is TRIM functions, which are commonly used by developers in WHERE clause.  For more understandings, let’s compare performance of two queries, one with TRIM function in WHERE clause and other one without TRIM functions.

USE AdventureWorks

GO

SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.*  fROM Production.WorkOrder wo

INNER JOIN Production.Product pr

ON PR.ProductID = wo.ProductID

WHERE LTRIM(RTRIM(pr.name)) = ‘HL Mountain Handlebars’

GO

SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.*  fROM Production.WorkOrder wo

INNER JOIN Production.Product pr

ON PR.ProductID = wo.ProductID

WHERE pr.name = ‘HL Mountain Handlebars’

Though outputs of both queries are same but first query took almost 99% of total execution time. This huge difference is just because of these trim functions so on production databases we must avoid these TRIM and other functions in both JOIN and WHERE clauses.