3

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?

sll
  • 61,540
  • 22
  • 104
  • 156
shashi
  • 4,616
  • 9
  • 50
  • 77
  • The standard answers are: A) Do you think it'll really matter? And B) If so, test it with your data, table structure, indexes, server, etc. :-) (But I'd expect the second to be more optimizable, which might be faster.) – T.J. Crowder Apr 05 '12 at 12:05
  • 1
    @T.J.Crowder: A it does (common SQL mistake). B no need because of A – gbn Apr 05 '12 at 12:14
  • @gbn: The answer to A is **not** necessarily that it matters. It depends on the data set (e.g., is the index important). Common mistake. ;-) – T.J. Crowder Apr 05 '12 at 14:50

1 Answers1

6

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

gbn
  • 422,506
  • 82
  • 585
  • 676