Alan Mil
Alan Mil

Reputation: 143

Difference in performance in SQL

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?

Upvotes: 5

Views: 127

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

  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.

Upvotes: 7

user275683
user275683

Reputation:

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

Upvotes: 4

Related Questions