Hi,

It was a nice article with a simple example and was very easy to understand the basic functionality. But one should not use functions in a WHERE clause (against the best practices). It should rather be used only in SELECT clause. WHERE clause can be re-written something like

WHERE HireDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, @Date), 0) -- START OF MONTH OF SEARCHED DATE
AND
DATEADD(ms,-3, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @Date)), 0)) -- END OF MONTH OF SEARCHED DATE

in order to hit the index. Cheers.

Best Regards,
Usman Butt

Hey Usman:

Thanks for the comments.  When I originally executed the SQL statement the table count was small and there was relatively no difference in the performance.  However, I executed a query on a table with 17 million records and noticed a significant difference in the IO price (see statistics below).  You are correct in that using the BETWEEN clause refers to an index.  You also revealed a good point in that SQL statements can be executed more than once, and it is good to analyze each statement's performance and cost to make sure you either making good use of indexes or create an index.

Tim

DATEDIFF

(151208 row(s) affected)

Table 'tbl_WeatherObservations'. Scan count 5

, logical reads 174086, physical reads 510

, read-ahead reads 173341, lob logical reads 0

, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 4585 ms,  elapsed time = 26785 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

BETWEEN DATES

(151208 row(s) affected)

Table 'tbl_WeatherObservations'. Scan count 1

, logical reads 173624, physical reads 346

, read-ahead reads 173341, lob logical reads 0

, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:

   CPU time = 2028 ms,  elapsed time = 25933 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Thanks for the tip.

A question when you say: It should rather be used only in SELECT clause. WHERE clause can be re-written something like

But I see the example still have dateAdd and dateDif in the where clause. So in the rewritten where clause, the index is the hiredate?

Thanks

Hi Anna,

I should rather re-state that

Wrapping table columns of WHERE or JOIN clause inside of any type of function (system or user defined) can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. But In my example the dateadd and datediff are used to just create the date range (no column of the table is used in the functions). I did use these functions in WHERE clause to keep the resemblance with the code and it would not affect the index hit. To make it clear we can re-write the code something like

Declare @StartOfMonth DATETIME, @EndOfMonth DATETIME

SET @StartOfMonth = DATEADD(mm, DATEDIFF(mm, 0, @Date), 0)

SET @EndOfMonth = DATEADD(ms, -3, DATEADD(mm, 1, @StartOfMonth))

And then the WHERE clause becomes

WHERE HireDate BETWEEN StartOfMonth AND @EndOfMonth

Hope this will clarify the situation.

And as far as index is concerned, yes, the index should be on hire date.

Moreover, you can also use a TALLY table OR Calendar table for such queries and may get better performance. Cheers.

Best Regards,
Usman Butt

Thanks Usman, this clearly answers my question. It is very helpful.