Parameter cause a table scan in SQL Server

Viewed 1504

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?

2 Answers
Related