What is hash match in execution plan?
The Hash Match represents the building of a hash table of computed hash values from each row in the input. From this MSDN article, this is the behavior of how that hash table is built. With a Hash match, you can expect to see the HASH:() and RESIDUAL:() predicates in an execution plan and potentially a probe row.
What is a hash join SQL?
The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table.
When can hash join be used?
Hash join is used when projections of the joined tables are not already sorted on the join columns. In this case, the optimizer builds an in-memory hash table on the inner table’s join column. The optimizer then scans the outer table for matches to the hash table, and joins data from the two tables accordingly.
How to calculate probe and residual in SQL?
Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len (a.col1)=10. But while going through another example, I could see both the probe and the residual to be the same column.
Why do I have a residual in my database?
Otherwise, you will see a residual as items in the hash bucket are tested for a match, not just a hash function match. Your test does not specify NULL or NOT NULL for the columns (a bad practice, by the way), so it appears you are using a database where NULL is the default.
When is a hash function perfect in SQL?
If the join is on a single column typed as tinyint, smallint, or integer * and if both columns are constrained to be NOT NULL, the hash function is ‘perfect’ – meaning there is no chance of a hash collision, and the query processor does not have to check the values again to ensure they really match.