SQL Efficiency - Query using dateAdd Function twice; or SubQuery and DateAdd Function once; on Date BETWEEN
I don't think it matters which of the two you use. And the Execution Plans agree.
But it seems you are doing calculations to column itsm_requiredbyx
and then check if the result is between two external values, @DateFrom
and @DateTo
. This way, all datetimes from this field are processed by the functions before the WHERE
conditions can be applied and no index can be used. The second link (Ten Common SQL Programming Mistakes) in @DOK's answer provide more detailed info on why and when this happens.
If you don't do any calculations to the column, but you do the (reversed) calculations to the external values instead and then check if itsm_requiredbyx
is between these two calculated values, the query can use an index of itsm_requiredbyx
(and the functions will only be called twice and not for every row in the table).