Jeff Moden (5/5/2014)
Steve Jones - SSC Editor (5/5/2014)
Jeff Moden (5/3/2014)
I think a much better question would be, "What determines what will be the first (top) table (according to books online) in a hash match join"? 😉There's no reliable way to determine this. It should be the smaller input most of the time, but sometimes the optimizer doesn't get that one and that usually means the DBA's phone rings with a complaint.
Yep... that's my whole point. It would be wonderful to see a QOTD where the correct answer is, "It Depends". 😛
Well, no, the correct answer in this case would be "The table which the optimizer thinks is smallest", because that is always the first input accessed by the hash join operator (even when the optimizer gets it wrong and this input is very much bigger than the other input so that it doesn't end up forming the build table).
Or did you mean "It depends what the optimizer thinks is best"?
Tom