I spent a while messing with this. And while there's not a way to get SQL to ignore a forced join type at the table level (see Kendra Little's excellent video and related links), there is something kinda sneaky you might be able to do to get them to change the code.
In SQL Server 2012 (SP3+), there are hints to toggle Memory Grants. Since Hash Joins are memory consuming operators, you could use a Plan Guide to severely limit the memory your query acquires, slowing it down immensely.
Take this simple example query:
SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;
The plan looks pretty normal.
The Hash Join stays in memory, because the query acquires a ~2.1GB memory grant.
Creating a bad plan:
If we create a plan guide and use the MAX_GRANT_PERCENT hint to cap the memory at 0...
EXEC sp_create_plan_guide
@name = N'brimful of hash joins',
@stmt = N'SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(MAX_GRANT_PERCENT = 0)'
GO
Our query gets a similar looking plan...
But now the Hash Join spills dismally to disk.
Because it got a nonsense memory grant of around ~8mb.
Further evidence of how awful this query got is in SET STATISTICS TIME, IO ON
No Guide:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9777604,
Table 'Workfile'. Scan count 0, logical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0,
SQL Server Execution Times:
CPU time = 40001 ms, elapsed time = 6992 ms.
Guided By Voices:
Table 'Users'. Scan count 7, logical reads 113953,
Table 'Posts'. Scan count 7, logical reads 9776298,
Table 'Workfile'. Scan count 3312, logical reads 362880, physical reads 56144, read-ahead reads 446000,
Table 'Worktable'. Scan count 0, logical reads 0,
SQL Server Execution Times:
CPU time = 66593 ms, elapsed time = 34270 ms.
The query with the rotten plan guide is in decidedly worse shape.
Is this a good idea?
Well, no, but it doesn't seem like that join hint is so hot, either.
For what it's worth, I tried some other stuff to get the hint to ignored, but they didn't seem to work (no plan change):
@hints = N'OPTION(LOOP JOIN, MERGE JOIN, HASH JOIN, TABLE HINT(p, FORCESEEK))'
Or resulted in an error:
@hints = N'OPTION(LOOP JOIN, MERGE JOIN, TABLE HINT(p, FORCESEEK))'
Msg 1042, Level 16, State 1, Line 64
Conflicting JOIN optimizer hints specified.
Msg 10516, Level 16, State 1, Procedure sp_create_plan_guide, Line 20 [Batch Start Line 59]
Cannot create plan guide 'brimful of hash joins' because @module_or_batch can not be compiled.
I don't expect you to do this, but it was an interesting enough question for me to want to come up with some response (note: not answer!) to.
And, you know, I thought the results were funny.
