On occasion I’ll see queries trim off whitespace from both the input parameters as well as against the columns in the predicate (WHERE clause).  If you’ve been reading my most recent posts I’ll start to sound like a broken record – applying functions to columns in a predicate will make a query unsargable (unable to efficiently take advantage of indexes).  If data is cleaned up at the application tier or even on insert or update to the database, you can clean up whitespace ahead of time.  This allows you to not worry about trimming whitespace when looking up data.

When I see the top queries on a server doing this I usually look at the raw data (if I have read access) to see how much data has whitespace around it.  A majority of the time it does not and this is just defensive programming that is causing a performance problem.

The demo query

The following query looks up customers by first name and last name.  It uses LTRIM and RTRIM to remove any whitespace that is in the column:
SELECT * FROM CustomerAccount WHERE LTRIM(RTRIM(AccountNbr)) = LTRIM(RTRIM(@AccountNbr))

Due to the function being applied to the AccountNbr column, it makes this query unsargable.  As a result we have an index scan on a large table:

This query took 78 seconds to execute.

Rewritten query

If we assume that the data in the table does not have superfluous whitespace, this query will be sargable and take advantage of indexes:

SELECT * FROM CustomerAccount WHERE AccountNbr = LTRIM(RTRIM(@AccountNbr))

This query took 1 millisecond to execute.

You’ll notice this query still has LTRIM and RTRIM on the input parameter.  I’d still suggest doing this in the application tier, but for demo purposes I’m showing how removing functions on columns improves performance and not on the parameters.

If you’re in a bind

Sometimes you don’t have the luxury of making code changes quickly, or just not at all (i.e. vendor products).  You have the option of creating an indexed computed column to get around this:

ALTER TABLE CustomerAccount ADD AccountNbrTrim AS LTRIM(RTRIM(AccountNbr))

CREATE INDEX IX_CustomerAccount_AccountNbrTrim ON CustomerAccount(AccountNbrTrim)

After creating the indexed computed column, the following plan shows it being used in a seek despite there being no code changes to the original query:

This query took less than 1 millisecond to execute.

Final thoughts

Ideally cleaning up data on input is the best way to go.  As always “garbage in garbage out”!