CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
GinjaNinja
GinjaNinja

Reputation: 806

Table Hint using WITH

I have the following query:

SELECT 
    *, 
    row_number() OVER (PARTITION BY CurveId, DeliveryDate, PeriodNumber 
                       ORDER BY publishdate ASC) AS [DuckCount]
FROM  
    mc.CURVEID_7017 
WHERE 
    CURVEID = 9152 
    AND DATEDIFF(hour,PublishDate, DeliveryDate) <= 24
OPTION (TABLE HINT(mc.CURVEID_7017, INDEX(IDX_CURVEID_7017)))

This works fine. Now I want to use it with a WITH keyword, but cannot get SQL Server to allow the table hint. It appears to not be valid within the WITH (...) section. If I move it outside, then it fails reporting that the table I'm hinting for is not part of the query. If anyone can show me how to perform the query below with the table hint I would be very grateful!

WITH filteredNew AS 
(
    SELECT 
        *, 
        row_number() OVER (PARTITION BY CurveId, DeliveryDate, PeriodNumber 
                           ORDER BY publishdate ASC) AS [DuckCount]
    FROM  
        mc.CURVEID_7017 
    WHERE 
        CURVEID = 9152 
        AND DATEDIFF(hour,PublishDate, DeliveryDate) <= 24
    /*OPTION (TABLE HINT(mc.CURVEID_7017, INDEX(IDX_CURVEID_7017)))*/
)
SELECT
    DeliveryDate, value 
FROM 
    filteredNew
WHERE
    [DuckCount] = 1 
    AND DeliveryDate BETWEEN '1-sep-2015' AND '10-Nov-2015 23:59:59'
ORDER BY
    DeliveryDate ASC

The HINT makes a large performance difference. Any help appreciated!

Details: SQL Server 2008 R2

Please comment if I can provide any further detail.

Upvotes: 3

Views: 860

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Move the hint to the end. See OPTION:

Only one OPTION clause can be specified with the statement.

The statement is the entire SELECT query together with any preceding CTEs - the entire statement is compiled and optimized as a whole.

WITH filteredNew AS (
SELECT *, row_number() 
OVER (PARTITION BY CurveId, DeliveryDate, PeriodNumber ORDER BY publishdate ASC) AS [DuckCount]
FROM  mc.CURVEID_7017 WHERE CURVEID = 9152 AND DATEDIFF(hour,PublishDate, DeliveryDate)<= 24)
select DeliveryDate, value from filteredNew
where [DuckCount] = 1 AND DeliveryDate BETWEEN '1-sep-2015' AND '10-Nov-2015 23:59:59'
order by DeliveryDate asc
OPTION (TABLE HINT(mc.CURVEID_7017, INDEX(IDX_CURVEID_7017)))

Upvotes: 1

Related Questions