SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul White / The Right Kind Of Join / Latest Posts

RE: The Right Kind Of Join

kalyani.k478 — Thu, 24 Jan 2013 21:17:04 GMT

Join hints is new to me n gonna learn this today..thanks for the question:-)

RE: The Right Kind Of Join

SqlMel — Fri, 23 Dec 2011 10:33:37 GMT

Got it wrong but it was a very good question. Need to research this.

RE: The Right Kind Of Join

Tee Time — Thu, 25 Aug 2011 06:10:07 GMT

Learning more and more each day! Thanks!

RE: The Right Kind Of Join

BuntyBoy — Wed, 24 Aug 2011 16:57:51 GMT

Got it correct...

RE: The Right Kind Of Join

kevin.l.williams — Wed, 24 Aug 2011 10:38:16 GMT

[quote][b]Ernie Schlangen (8/24/2011)[/b][hr]I was able to look this up and find the right answer but I'd be lying if I said that I understood what is going on here. Is there an article that explains the join hints? If not, perhaps that's a good area for a future article. MSDN wasn't particularly helpful to me on this. I suspect that they expect me to have some base level of knowledge that I'm lacking. :crying:[/quote]Typically SQL server selects the best execution plan for a query and MS recommends that join hints be used as a last resort. Check out the See Also links on the Join Hints BOL page[url]http://msdn.microsoft.com/en-us/library/ms173815.aspx[/url]

RE: The Right Kind Of Join

skrilla99 — Wed, 24 Aug 2011 10:09:30 GMT

Thanks for the question!

RE: The Right Kind Of Join

OzYbOi d(-_-)b — Wed, 24 Aug 2011 09:17:46 GMT

thanks - great question :-)

RE: The Right Kind Of Join

Daniel Bowlin — Wed, 24 Aug 2011 08:08:23 GMT

Really interesting question, thanks

RE: The Right Kind Of Join

KWymore — Wed, 24 Aug 2011 07:08:20 GMT

Nice question. I look forward to more questions related to join hints.

RE: The Right Kind Of Join

Kameswari Aravindh — Wed, 24 Aug 2011 05:55:02 GMT

Good Question on Join Hints. :-)

RE: The Right Kind Of Join

Ernie Schlangen — Wed, 24 Aug 2011 05:54:27 GMT

I was able to look this up and find the right answer but I'd be lying if I said that I understood what is going on here. Is there an article that explains the join hints? If not, perhaps that's a good area for a future article. MSDN wasn't particularly helpful to me on this. I suspect that they expect me to have some base level of knowledge that I'm lacking. :crying:

RE: The Right Kind Of Join

Britt Cluff — Wed, 24 Aug 2011 05:39:49 GMT

Learned something new again today, thanks.

RE: The Right Kind Of Join

rfr.ferrari — Wed, 24 Aug 2011 05:30:39 GMT

nice question!

RE: The Right Kind Of Join

Kiara — Wed, 24 Aug 2011 05:27:26 GMT

Nice. I had to work for this one - learned quite a bit in the process. Thanks!

RE: The Right Kind Of Join

Paul White — Wed, 24 Aug 2011 04:36:36 GMT

[quote][b]Andrew Watson-478275 (8/24/2011)[/b][hr]Paul - that's exactly what I did when BOL didn't quite give enough information to answer. I agree with your view on the 2 point question...[/quote]No worries :-)

RE: The Right Kind Of Join

Paul White — Wed, 24 Aug 2011 04:34:38 GMT

[quote][b]Tom.Thomson (8/24/2011)[/b][hr]Isn't the inability to apply the loop hint to a right join a bit bizarre though?[/quote]I would normally answer this, but today's question is one of three... ;-)Question 2 will appear next week (1 September), and question 3 a week later.

RE: The Right Kind Of Join

L' Eomot Inversé — Wed, 24 Aug 2011 03:47:59 GMT

Good question.Isn't the inability to apply the loop hint to a right join a bit bizarre though? Although it seems pretty obvious why this happens, there is in fact something odd about the exclusion of right join. After allA right join B on A.x=B.xis identical in meaning to B left join A on A.x=B.xand I can apply a loop hint to the second. So I ought to be able to apply it to the first, unless the optimiser interprets the loop hint as saying "use a nested loop join where the [strike]out[/strike]inner loop is on the right hand component of the join, that is the table which occurs second in the text of the query" rather than just "use a nested loop join (choosing whichever component the optimiser thinks best for the outer loop)". The optimiser has great freedom generally, and making the loop hint specify more of a constraint on the optimiser than it really needs is out of tune with that usual freedom.edit: "outer" should be "inner", see text struck and replaced above

RE: The Right Kind Of Join

Andrew Watson-478275 — Wed, 24 Aug 2011 03:45:22 GMT

Paul - that's exactly what I did when BOL didn't quite give enough information to answer.I agree with your view on the 2 point question; sorry for doubting you.(In real life, they're all 2+ point questions - you never get it handed on a plate).

RE: The Right Kind Of Join

Kwex — Wed, 24 Aug 2011 03:44:09 GMT

Nice! Learnt something new today about hint limitations on RIGHT joins :-)

RE: The Right Kind Of Join

Paul White — Wed, 24 Aug 2011 03:30:52 GMT

[quote][b]Andrew Watson-478275 (8/24/2011)[/b][hr]There would have been less guesswork involved if the full error message had been given instead of just an extract[/quote]You can get the full error text without running the query by checking sys.messages:[code="sql"]SELECT m.[text] FROM sys.messages AS m WHERE m.message_id = 8622 AND m.language_id = 1033[/code]I'm comfortable that the question and answers had the right level of detail to make it interesting (it is a 2 point question after all).

RE: The Right Kind Of Join

CoolCodeShare — Wed, 24 Aug 2011 03:17:34 GMT

Nice question.

RE: The Right Kind Of Join

Koen Verbeeck — Wed, 24 Aug 2011 03:10:45 GMT

Great question, thanks.

RE: The Right Kind Of Join

Andrew Watson-478275 — Wed, 24 Aug 2011 02:49:30 GMT

There would have been less guesswork involved if the full error message had been given instead of just an extract:[quote]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.[/quote]

RE: The Right Kind Of Join

adb2303 — Wed, 24 Aug 2011 02:41:25 GMT

Pure guess work from me...

RE: The Right Kind Of Join

Stewart "Arturius" Campbell — Wed, 24 Aug 2011 01:39:40 GMT

Interesting question, thanks

RE: The Right Kind Of Join

kapfundestanley — Wed, 24 Aug 2011 00:36:25 GMT

Good question.

RE: The Right Kind Of Join

SQLRNNR — Tue, 23 Aug 2011 22:55:17 GMT

Nice question

RE: The Right Kind Of Join

bitbucket-25253 — Tue, 23 Aug 2011 21:48:54 GMT

Very nice ...

The Right Kind Of Join

Paul White — Tue, 23 Aug 2011 21:48:06 GMT

Comments posted to this topic are about the item [B]The Right Kind Of Join[/B]