AP 07 - Avoid using functions in the WHERE clause

Caution

Performance

There are two reasons to avoid using a function on a column inside a WHERE clause.

It is CPU intensive because the function gets called for each record in your result set.
It prevents the database from applying an index, sort key or cluster key. As a result, the database needs to read more data from the storage layer. This can have a significant impact on query performance.

The following example demonstrates the anti-pattern in SQL Server. A function wraps around the date_of_birth column.

SELECT emp
  FROM employee
 WHERE DATEADD(d
          ,30
          ,date_of_birth)>GETDATE();

Rewrite your query to push the function to the other side of the operator.

SELECT emp
  FROM employee
 WHERE date_of_birth > dateadd(d,-30,getdate())

One alternative to this would be to pre-calculate the function as part of ETL.

In some databases, such as Oracle, you can create indexes based on functions.

In some situations, you may not be able to avoid using the function in the WHERE clause.
Your data volumes are small.
The column you filter against does not have an index or cluster key
You have created a function based index for the function on the column