 Hi there, this is Krishnanj from Bytescout and in this video we will be going to learn about the next physical operator of TSQL execution plan called an adaptive join. The adaptive join was introduced in Azure SQL Database and in SQL Server 2017. It is a new join type that can enable the choice of a hash join or nested loop join method at runtime until after the first input has been scanned. So a query plan can dynamically switch to a better join strategy during execution time to decide when to switch to a nested loop join or a hash join based on the threshold defined in it. So as a query executes and it is determined that it has either met the threshold level or exceeded it, the processing continues down the correct branch of the adaptive join. So the way it works is that it starts out building an adaptive buffer of join input and if the join input exceeds a specific row count threshold then your plan continues with a hash join and if the row count of the join input is small enough of threshold that a nested loop join would be a more optimal than a hash join then the plan switches to a nested loop join. Here one thing to note is the adaptive join is applicable only to column store index scan which is used to provide rows for the hash join build phase. Now let's see this join in action. Alright so now before executing this query first I need to create a non-clustered index on this cells order header table. Well as I said earlier that it is mandatory to create a column store index to use the adaptive join. So first let me create this column store index and now before we go ahead this is the same query which we have used in the earlier demo. The only difference in this demo is the change in the where close. So here in the where close I have specified the territory id equal to one. Now let's execute this query and now let's check the execution plan of this query. So here we can see that our query is using a new adaptive join operator. Now this adaptive join operator uses a threshold that is used to decide when to switch to a nested loop join or a hash join plan. So in this example when I hover over this adaptive join operator here you can see the threshold row count is 1074 rows. So that means anything with greater than or equal to 1074 rows will use a hash join and if the less than the threshold row count then a nested loop join will be used. Now in our case this query is returning the 16,865 rows. So that means this exceeded the threshold row count limit and hence we have a standard hash join operation which you can see over here and now let's modify the where close. So over here instead of territory id let me add the customer id and now let's execute this query again and in the execution plan tab when I hover over this adaptive join operator again you can see that the adaptive threshold row count is 391 but our query is returning only 10 rows which is less than the threshold row count. So that means we have a nested loop join operation which you can see over here in the adaptive join type. So technically this adaptive join does not represent a new type of join but the behavior of dynamically switching between the two core types which is nested loop and hash match makes this effectively a new join type.