5

I have a date column on SQL Server table called dd.

dd
---------------------------
10-01-2015 00:00:00.000
22-05-2015 10:22:32.521
27-05-2015 12:30:48.310
24-12-2014 09:51:11.728
27-05-2015 02:05:40.775
....

I need to retrieve all rows where dd value is from the last 24 hours.

I found 3 options for filtering to get the result needed:

1. `dd >= getdate() - 1`
2. `dd >= dateadd(day, -1, getdate())
3. `dateadd(day, 1, dd) >= getdate()

My questions are: Are all the 3 options will retrieve all rows I need?
If so what is the difference between them?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Alan Mil
  • 143
  • 1
  • 2
  • 10
  • 6
    #3 is the worst you can do! – JimmyB May 27 '15 at 11:28
  • 1
    Have you looked at the execution plans? –  May 27 '15 at 11:30
  • #1 and #2 are identical, where #1 is just MS-SQL's shorthand notation for #2. I'd use #2 though, because it seems to convey more clearly to the reader what unit is being added. – JimmyB May 27 '15 at 11:35
  • *"I have a date column..."* No, you don't. `date` columns cannot contain time components. You probably have a `datetime` column. – Heinzi Dec 10 '17 at 10:17

2 Answers2

7
  1. dd >= getdate() - 1

This is something like a hack, but it works, but sometimes it can lead to errors(http://www.devx.com/dbzone/Article/34594/0/page/2).

  1. dd >= dateadd(day, -1, getdate())

This is standard way of doing things.

  1. dateadd(day, 1, dd) >= getdate()

This will also work but there is one NO. It will not use index if any index is created on that column. Because it is not a Search Argument(What makes a SQL statement sargable?). When you apply an expression to some column it becomes non SARG and will not use any index.

All 3 version will produce same result, but first is hack and in some cases will lead to bug. Third will not use index. So it is obvious that one should stick on option 2.

Community
  • 1
  • 1
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
4

First two are exactly like Giorgi said, but on the third one your Index Seek will become Index Scan. SQL Server will still use that index but it is no longer able to jump to specific record but instead it has to scan it to find what it need.

For the purpose of demonstration I selected the table that had DATETIME column indexed and only selected that column to avoid any key lookups, and to keep plan simple.

enter image description here

Also take a look at reads on the table and estimated vs returned row count. As soon as you wrapped the column in a function it is not able to estimate correct number of rows which will cause large performance issues when queries become more complex.

enter image description here