Functions like “getdate()” and convert(varchar(10), MyDateColumn,101) commonly show up in the WHERE clause of queries.  Here’s an example:

select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < getdate()

You might think “no big deal.” It’s just a function, but for sql server, it almost always turns a deterministic query into a non-deterministic one.  The difference?  Index usage.

Most of the time, sql server will ignore any index on a column that is used in a WHERE clause expression containing a function, turning an index seek into an index or table scan, with the obvious performance penalty.

So instead of the above query, replace it with:

declare  @CurrentDate datetime
select   @CurrentDate = getdate()   

select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < @CurrentDate

The function is gone and sql server will use an index on LastUpdated if it exists.

The same holds true for this example:

select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < dateAdd(day, -1, getdate())

becomes:

declare  @CurrentDate datetime
select @CurrentDate = dateAdd(day, -1, getdate())select   c.LastName
from     Customer c with (nolock)
where    c.LastUpdated < @CurrentDate

Sometimes there is simply no workaround for a function in the WHERE clause but, knowing their impact, make every effort to eliminate them.