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.
Grant provides the best answer, but it makes no difference who says what. There are many occasions where "it depends" and technical documentation provides a simplified answer that at best is only generally true or an answer that's intended to be true according to specifications but isn't actually. Which input SQL Server will choose for the hash table can be easily confirmed by setting up a couple of sample tables, one big and one small, then examine the execution plans for multiple selects with different join configurations. My eyes are my most trusted source.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho