I have the following query:

    , afp.period_end
    , COUNT(*)
from services s
    Left join
            select entityId, resolutionDate
            from ...             
        ) SI on si.entityId = s.Id
    left join #aff_periods afp -- or left hash join #aff_periods afp
        on isnull(si.reslutionDate, GETDATE()) <= afp.period_end
group by afp.period_start
    , afp.period_end
option (hash join); -- or this

Whenever adding either the query-level hint or join-level hint I get the following error.

Msg 8622, Level 16, State 1, Line 50

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I've read some posts online suggesting that this might be because of a index on the table, so I dropped the non-clustered index I had created on that table and tried to re-run the query but I still get the error.

Any suggestions what I could try or why this is happening for a temp table?


1 Answer 1


What you're running into isn't because of the temp table, it's because of the type of join you've chosen with the hint you're forcing.

Both Merge and Hash joins require at least one equality predicate to run.

For more information, check out these old (but awesome) blog posts from Craig Freedman.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.