Which Where condition on Datetime will be faster?
sql, sql-server, sql-server-2005, t-sql
Solution
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
Problem
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?