robert.diley (5/14/2014)
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.From p. 88:
"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."
I am inclined to trust this source.
It's defintely a good source, but please check the detailed post I made earlier in this discussion. There is a difference between what the optimizer attempts (which is to make sure that the smaller table is used as the top, build input) and the reality (where the first, topmost input is used for the build phase, even when the optimizer got it wrong).
BTW, I have been asked to be the technical editor for the next edition of Grant's book; I hope that makes me an equally trustworthy source.