Tips & Tricks: Don’t use UPPER or LOWER function in optimized queries

As a certain project at work evolves I find that more and more of my time is spent on optimizing the T-SQL queries that are utilized. Recently I have come across the issue that an index was not getting utilized in a sql query when I was using the UPPER or LOWER function to do an ignore case comparison. As it turns out that using a function on a column causes a table scan and will not use the index ever.

To resolve this issue we fell back on regular expressions as T-SQL does not support the ILIKE function. What this means is that we can use regular expression sets for each character in our String. To illustrate lets look at the following basic SQL string.

SELECT * FROM USERS WHERE UPPER(LAST_NAME) LIKE "DENTON%"

If our table had an index on the LAST_NAME column then it would be ignored. To use our index we would have to rewrite the query to be:

SELECT * FROM USERS WHERE LAST_NAME LIKE "[dD][eE][nN][tT][oO][nN]%"

This may seem odd, but running this second query will utilize the column index. Another item to note is that you can only use the regular expressions in LIKE clauses and not = clauses.

Resources

// T-SQL // Tips & Tricks //

Comments & Questions

Add Your Comment