An Overview of Adaptive Joins

DZone Database Zone

An Overview of Adaptive Joins

I was surprised to find out that a lot of people hadn't heard about the new join type, Adaptive Join. So, I figured I could do a quick overview.

Adaptive Join Behavior

Currently, the Adaptive Join only works with columnstore indexes, but according to Microsoft, at some point, they will also work with rowstore. The concept is simple. For larger datasets, frequently (but not always... let's not try to cover every possible caveat — it depends, right?), a hash join is much faster than a loops join. For smaller datasets, frequently, a loops join is faster. Wouldn't it be nice if we could change the join type on the fly so that the most effective join is used depending on the data in the query? Ta-da! Enter: the Adaptive Join.

more info...