SQL Server Performance Forum – Threads Archive

DateDiff Vs Date1 > Date2

Hi All I have one doubt on the performance issue.
Suppose in a SQL Query 1 I have SELECT * FROM tblX WHERE dtDate1 > dtDate2 and in the other query no. 2 I have is SELECT * FROM tblX WHERE DATEDIFF(minute,dtDate2,dtDate1) > 0 Now which one will give me better performance?
Also what effects both will have on the use of indexes (if any) by each query on dtDate1 and dtDate2. Thanx in advance

---

Execution plan show me the same plan for both (In my database)
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

But When I tried it on the SQL Profiler I got different results for both.
What is concerning me whether DateDiff will use any indexes if available

---

So in your excution plan DateDiff use indexes and where… not? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

Hi ya, datediff will definitely not use any index (except perhaps a clustered index scan, a.k.a. a table scan in disguise, or a covered index if only the dates are selected back) dtDate1 > dtDate2 can use a composite index on the date fields Cheers
Twan PS note that the two statements are not quite the same, since the datediff is accurate down to the minute, wherease the > is accurate down to sub-second

---

]]>