Two scripts - when and how to apply functions to the WHERE clause

Question

WH.dbo.vw_FactTable is a huge table 100mill rows with good indexing on DateKey

This runs almost instantaneously:

1.

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   DateKey >= 20130101 AND
        DateKey <= 20130110 

This takes two minutes:

2.

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   CONVERT(DATETIME,CONVERT(CHAR(8),DateKey,112)) >= '01 JAN 2013' AND
        CONVERT(DATETIME,CONVERT(CHAR(8),DateKey,112)) <= '10 JAN 2013' 

Why so different?

The execution plans are quite different - 1 seems to choose Merge Join:

Whereas 2 goes for a Hash Match:

Is there an obvious way to tune query 2?

Answer 1

In the second query, as you are using the function in the where clause(Convert), outcome of this function is evaluated at the run time, and the SQL Server Query engine has to scan the whole table to get necessary data.

Answer 2

People have hit it on the head already in comments, restructuring the field neuters the index, this would perform similarly to your first query:

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   DateKey BETWEEN CAST(CAST(REPLACE(CAST('01 JAN 2013' AS DATE),'-','')AS VARCHAR(12))AS INT)
                    AND CAST(CAST(REPLACE(CAST('10 JAN 2013' AS DATE),'-','')AS VARCHAR(12))AS INT)