Why Functions in WHERE clauses should be avoided

Consider we have the following query:

SELECT ModifiedDate FROM Contacts 
WHERE DATEDIFF(MINUTE, ModifiedDate, GETDATE()) > 0

This query uses the DATEDIFF function, and when we look at the query plan we can see a Index scan is being performed.

2017-12-18_12h31_43

The issue with an index scan is that all rows are reviewed before returning a result

And now consider this query:

SELECT ModifiedDate FROM Contacts 
WHERE GETDATE() > ModifiedDate

Since we are not using a function in the WHERE clause, this query uses an Index seek.

2018-01-10_17h13_47.png

An index seek is much more efficient then an index scan when there is an index on the column

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s