What's the difference between the following two T-SQL queries, assuming ufnGetSomeDate() is very COSTLY?
------------------------------------------------------------------------------------------
SELECT * FROM SomeTable WHERE
DATEDIFF(mi, ufnGetSomeDate(), CreatedDate) BETWEEN 1 AND 60
------------------------------------------------------------------------------------------
SELECT * FROM SomeTable WHERE
CreatedDate BETWEEN DATEADD(mi, 1, ufnGetSomeDate()) and DATEADD(mi, 60, ufnGetSomeDate())
------------------------------------------------------------------------------------------
If they look just the same to you, congratulations, because you will learn something today.
It turns out that the performance of the first query is far worse than the second one.
At first I thought, well the first query calls ufnGetSomeDate() once and the second calls twice; the first one got to be better.
Unfortunately that's very wrong, the second query does call twice, however, the first one calls ufnGetSomeDate() as many time as the number of records in SomeTable.
DATEDIFF(mi, ufnGetSomeDate(), CreatedDate)have to be called for EVERY record in order to determine wether the result is between 1 and 60.
