SQL Efficiency - Query using dateAdd Function twice; or SubQuery and DateAdd Function once; on Date BETWEEN

Question

The expense is using the DateAdd function not only in the SELECT, but also in the WHERE; Or using the Sub Query which initially returns more data than I need, but then can be filtered without using the DateAdd function again on the database.

Execution Plan seems to imply that they are identical as far as it is concerned. I'm wondering which would be more efficient?

    DECLARE @DateFrom DateTime
    SET @DateFrom = '2011-05-27'
    DECLARE @DateTo DateTime
    SET @DateTo = '2011-06-27'

    SELECT id, name, 
    dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
            dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM')) as itsm_requiredbyx
    FROM tablename
    WHERE dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
            dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM'))
            BETWEEN @DateFrom AND @DateTo

    ORDER BY itsm_requiredbyx desc

    ---------------------------------------------------------------------------------------------

    SELECT *
    FROM
        (
        select id, name, 
        dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
                dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM')) as itsm_requiredbyx
        from tablename 
        ) RR
    WHERE itsm_requiredbyx BETWEEN @DateFrom AND @DateTo
    ORDER BY itsm_requiredbyx desc

Answer 1

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).

Answer 2

This article might help you choose. If your date columns are indexed, there might be a big difference between the methods used, particularly in the WHERE clause.

As it says,

If you are searching large tables with lots of records, you will most likely index some of the date columns that are commonly used to constrain queries. When a date column is used in a WHERE clause, the query optimizer will not use an index if the date column is wrapped in a function.

This is also explained in Ten Common SQL Programming Mistakes, particularly under #2 Functions on indexed columns in predicates:

The problem arises from the fact that the index columns are being passed to a function, which the query engine must then evaluate for every single row in the table. In cases such as these, the WHERE clause predicate is deemed "non-SARGable" and the best that the query optimizer can do is perform a full index or table scan.

To make sure the indexes get used, we need to avoid the use of functions on the indexed columns.