Is it possible to force an adaptive join with a query hint? Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.

I have a parameterised query which has a sub-query that typically selects almost no rows (0 to 4), but occasionally picks 100k.

I believe that this is exactly the type of scenario that an adaptive join is designed for, but SQL Server doesn't pick this as a query plan.

A query plan of always-hash-join is server-destroying, so my only recourse at the moment is to force the join to be nested loops, and accept that individual worst-case requests will take a bunch of extra seconds to run.

I have an Azure SQL database at compatability 150, and BATCH_MODE_ON_ROWSTORE=1

Further details

For some reason paste-the-plan doesn't think the XML for this query is legit.

The query at issue is:

with
hashes as (select h.[ContactHash]
    FROM HashTable h
        join someTable p on h.id=p.id
    group by [ContactHash]
    having count(distinct key) &gt;1 
        and count(distinct p.thing) between 1 and 20),
dupeContacts as 
    (select distinct c.key,c.ContactHash from someTable c 
    where c.[ContactHash] in (select t.[ContactHash] from hashes t ) and c.key =  @id 
    )
    insert  into @MyTableVar
select * from dupeContacts


The two tables involved are similar to:

HashTable (
    int id ,
    ContactHash varbinary(16)
) -- millions, typically 4:1 to someTable by ID


and

SomeTable (
    int id Primary Key , 
    varchar(100) thing
)
As it says in the documentation, adaptive join currently supports SELECT statements only:


  Adaptive join eligible statements
  
  A few conditions make a logical join eligible for a batch mode Adaptive Join:
  
  
  The database compatibility level is 140.
  The query is a SELECT statement (data modification statements are currently ineligible).
  The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
  The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).