I have two tables a, b, primary key is their index.
Requirement:
If @filter is empty, select all records of a, b, else split @filter by any specific separator, find records whose b.PKey are in the filter.
Current Implementation:
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))
I found the last statement and (@filter = '' or b.PKey in (select item from splitFunction(@filter)) will always cause a table scan on table b, only if I remove @filter='', it will change to index seek.
Is there any way can implement my requirement and does not harm the performance?