SQL performance difference

Question

I have a date column in a 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 get all the rows where the dd value is indicated in the last 24 hours.

I found 3 filtering options to get the desired result:

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

My questions: Will all three options get all the rows I need?
If so, what is the difference between the two?

Answer 1

• dd >= getdate() - 1

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

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

This is the standard way to do things.

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

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

All 3 versions will give the same result, but hack first, and in some cases lead to an error. The third will not use the index. Therefore, it is obvious that you need to stick to option 2.

Answer 2

The first two are exactly the same as Giorgi said, but in the third case, your Index Seek will become Index Scan. SQL Server will still use this index, but it will no longer be able to go to a specific record, but instead, it should scan it to find what it needs.

For demonstration purposes, I selected a table with an indexed DATETIME column and only selected this column to avoid any key searches, and to make the plan simple.

See also the reading in the table and the counted number vs rows returned. Once you wrap the column in a function, it will not be able to evaluate the correct number of rows, which will cause big performance problems when the queries become more complex.