Which is better to compare dates < or DateDiff?

问题:

Which is considered better?

select *
from TableA
where productDate < '12/9/2013'

or

select *
from TableA
where DATEDIFF(day, productDate, '12/9/2013') > 0

While skimming thru articles, I read that using Date functions (ex: datediff, dateadd) in where clauses affected performance negatively. Is this true?

回答1:

The best will likely be:

SELECT <column list> -- not * (1)
  FROM dbo.TableA -- please always specify schema (2)
  WHERE productDate < '20131209'; -- always use a semi-colon (3)
    -- and always use a non-regional, unambiguous date format (4)

The reason this will be best is because it gives the optimizer the best chance to use an index on the productDate column. Even if an index doesn't exist today, someone may add one tomorrow. Applying functions like DATEDIFF() to the column will always make the expression non-sargable, meaning it will always have to use a less efficient scan (assuming that's the only search predicate).

As for the inline comments:

1. Bad habits to kick : using SELECT * / omitting the column list

2. Bad habits to kick : avoiding the schema prefix

3. Ladies and gentlemen, start your semi-colons!

4. Bad habits to kick : mis-handling date / range queries

回答2:

When you apply a function to a column you are unable to use any indexes available on this column.

Therefore the use of DateDiff() on the productDate will be less efficient than a less-than operator