For example

Declare @Username nvarchar(50)

Declare @SearchType nvarchar(50)

Declare @FilterValue nvarchar(50)

DECLARE @CID smallint

Set @Username = 'abc '

Set @SearchType = 'abc'

Set @FilterValue = N'pap'

DECLARE @ModeSearch TABLE

(

PID int PRIMARY KEY,

CID smallint

)

INSERT INTO @ModeSearch(PID, CID)

SELECT DISTINCT P.PID,

Patient.CompanyID

FROM P WITH (READPAST)

JOIN PSD WITH (READPAST) ON

P.PID = PSD.PID

AND P.CID= PDS.CID

WHERE PSD.LastUnitMode IS NOT NULL

AND PSD.LastUnitMode LIKE '%' + @FilterValue + '%'

AND P.PID IN (SELECT PID FROM dbo.fnGetSearchPatients(@SearchType,@Username,@CID))

The execution plan as follows

There is nested loop join with 0% cost (which is good)

But when we see the retrieval of data from P Table and PSD table

There is Index Seek on both P and PSD table

Cost is 11% and Actaul number of rows is 1

But actual number of rows is 10,00