gbritton1 (5/1/2014)
So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build the hash table from the smaller input to minimize work.
Most likely this is convergence not conflict.
Here is a 2008 R2 article that says the same thing as the old one.
This and the last link I posted are actually listed as part of the SQL 2014 BOL but where not changed.
http://technet.microsoft.com/en-US/library/ms189582(v=SQL.105).aspx"> http://technet.microsoft.com/en-US/library/ms189582(v=SQL.105).aspx
For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team.
There are some sources that say the QA will try to choose the smaller set as the build input, but here is some truth from SQL 2012 BOL.
http://technet.microsoft.com/en-us/library/ms173815.aspx
When using the HASH Join hint:
If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords
So the first table in the ON statement is used unless REMOTE table exists or is specified.
IMHO: Considering the Join Hint documentation for HASH, the Query Optimizer would have to change the join order so that the smaller set is first (like any good Query writing utility would do) to do what the other article says it is doing.
Note: I could not find anything in the BOL articles unique to SQL 2014 so we are left to guess that SQL 2014 BOL just has pointers to the old articles because the information has not changed.