Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this fairly straight-forward snippet of a query:

DECLARE @ClientGUID numeric(16) = 9000011916900200;

SELECT
  ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN
FROM Client c
INNER JOIN ClientID mrn
  ON mrn.ClientGUID = c.GUID AND mrn.TypeCode = 'MRN1'
INNER JOIN Chart ch ON ch.ClientGUID = c.GUID
WHERE c.GUID = @ClientGUID

This throws a "No Join Predicate" warning on the join between Chart and Client because the optimizer pushes the variable into the index seek predicate leaving nothing to join on and essentially doing a CROSS JOIN after the index seek. I get that this is a good thing.

My question is, if in six months someone sees that warning and starts freaking out, how can i tell the optimizer that this okay (intended behavior) and not to throw the warning?

I've tried changing the INNER JOIN to a LEFT JOIN (which I saw on another question about this warning), CROSS APPLY, and an explicit CROSS JOIN (and adding the condition in the WHERE clause) -- none of them get rid of the blasted warning.

I see this issue on both @@VERSION:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1765.0 (X64) Enterprise Edition (64-bit)
Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) Enterprise Edition: Core-based Licensing (64-bit)

Help!

share|improve this question
    
How about adding AND ch.ClientGUID = @ClientGUID to the WHERE clause? Sometimes you can be redundant to give SQL Server as much information as you can... –  Aaron Bertrand Mar 31 at 18:21
    
@AaronBertrand: nope. which makes sense, since that's what the optimizer is doing on its own. –  JoeNahmias Mar 31 at 18:23
    
Well, I've seen cases where adding redundant info does help the optimizer. How about adding both conditions to the ON clause instead of the WHERE, or in addition to? I don't really like that, but it's worth a try. Also, how about embedding a comment in the query text that explains there is a silly warning that should be ignored, even if you can't figure out a way to prevent the warning? –  Aaron Bertrand Mar 31 at 18:24
    
@AaronBertrand: no help in moving the conditions to the ON clause. Also, SQL Server 2012 throws the same warning. What's puzzling is that explicitly doing it as a CROSS JOIN doesn't quiet the warning -- but I guess that's the optimizer re-write for you... –  JoeNahmias Mar 31 at 18:28
    
Do these GUIDs have unique constraints? –  ypercube Mar 31 at 18:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.