SQL Server Performance

DateDiff Vs Date1 > Date2

Discussion in 'T-SQL Performance Tuning for Developers' started by kate_for_u, Sep 30, 2003.

  1. kate_for_u New Member

    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
  2. Luis Martin Moderator

    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
  3. kate_for_u New Member

    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
  4. Luis Martin Moderator

    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
  5. Twan New Member

    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

Share This Page