Adaptive Join in SQL Server 2005/2008

Finding the best query plan is most definitively the most difficult mission for SQL Server. SQL Server 2017 brings a entire new operator: Adaptive Join, This unify in a single unit: NESTED LOOPS and HASH thus: it introduced basically the ability to sense a bad join choice in a plan and then dynamically switch to a better join strategy during execution.
Adaptive joins feature enables SQL Server to select the join between the hash join or nested loop join method until the after the first input has been scanned/read. This creates a threshold that will be used to decide when we will switch to a nested loop plan. This session will teach you some simple solutions to implement adaptive join in old version of SQL Server from dynamic sql to plan guides.

Adaptive join Query Optimization