Sql-server – Forcing a hash join on a temporary table generates error

sql serversql-server-2008-r2

I have the following query:

select
    afp.period_start
    , 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?

Best Answer

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.

Introduction to Joins

Nested Loops Join

Merge Join

Hash Join

Related Question