Which Where condition on Datetime will be faster?

Question

I have a Datetime column called CreatedOn in a table. CreatedOn is also part of a non-clustered index where the order is descending.

Previously in the where condition I had a condition as follows

WHERE DateDiff(d,CreatedOn,GetDate()) < 180 AND ... other conditions

I changed this to

WHERE CreatedOn > '2012-04-04 00:00:00.000' AND ... other conditions

where I am calculating the cutoff date in C# code and then putting that in the adhoc query.

According to me, the second condition should be faster but I do not yet see a significant change in query execution times. But as the size of the table grows, which one will run faster?

Answer 1

The second form.

Putting functions on columns invalidates use of indexes (in almost all cases, so simply follow this rule always). See "Ten Common SQL Programming Mistakes", number 2