Parameter cause a table scan in SQL Server
<p>I have two tables a, b, primary key is their index.</p>

<p><strong>Requirement:</strong></p>

<p>If <code>@filter</code> is empty, select all records of a, b, else split <code>@filter</code> by any specific separator, find records whose <code>b.PKey</code> are in the filter.</p>

<p>Current Implementation:</p>

<pre><code>declare @filter nvarchar(max)= ''

SELECT * 
FROM a
JOIN b ON a.PKey = b.aPKey
       AND (@filter = '' OR b.PKey IN (SELECT item FROM splitFunction(@filter))
</code></pre>

<p>I found the last statement <code>and (@filter = '' or b.PKey in (select item from splitFunction(@filter))</code> will always cause a table scan on table b, only if I remove <code>@filter=''</code>, it will change to index seek.</p>

<p>Is there any way can implement my requirement and does not harm the performance?</p>