JOIN on LIKE when input udtt count is > 1
I'd start by breaking this out to get a better idea of which part is slow. This should clarify the execution plan overall.

From there you can start sorting out other techniques, like temp tables, recompile hints, indexes, etc.

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )

SELECT b.* 
FROM @inputUDTT a
INNER JOIN Tabl b 
ON (@inputCount &gt; 1 AND b.name = a.val)

UNION ALL       

SELECT b.* 
FROM @inputUDTT a
INNER JOIN Tabl b 
ON (@inputCount &lt; 2 AND b.name like '%' + a.val+ '%')


For example, in the first query where you have an equality predicate, the optimizer can choose different join types (though it may only ever choose nested loops because of the low row estimates for a table variable. In the second query, the only join type available with no equality predicate is nested loops.

Hope this helps!