The query below is using the StackOverflow2010 sample database:
DECLARE @Id INT = 18471
SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id
With a hash join hint, it returns the following error:
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
It works when I add OPTION(RECOMPILE)
or replace the variable with a string literal.
What could be causing this issue?
HASH JOIN
for this query? WithComments.UserId
fixed,Users.Id
is fixed, too. Whereas there is only one user with any specifiedId
, the whole join operation is redundant. Futhermore, theUsers
table is not required at all:SELECT UserId FROM Comments WHERE UserId = @Id
. Even futhermore, the query does not make any sense to me because it returns the givenUserid
in as many rows as there are comments by that user. What is the use of this redundant data? – Ant_222 Oct 17 '20 at 12:57