To summarize: the application sends a TFV where each row is a set of search parameters, where a null value in the TVF functioning as a "wildcard" indicate no filtering on that attribute. The goal of the query is to return all the rows in the target table that match any of the rows in the input TVF.

So if the TVF sends (Id=123,Name=null),(ID=null,Name='Joe'), the procedure should return all rows match either the first set of criteria or the second.

>Am I expecting too much of the optimizer ?

Yes. For this to work well, the QO would neeed to create a seperate plan for each row in the input TVF, and it simply was never build to do that. For each row in the TVF a table scan will be required, as no single index can be used to evaluate the join criteria.

So you actually need to run a separate query for each row in the input TVF. You can cursor over them and load a temp table, reducing this to an iterative form of a classic dynamic search query, which you can use Dynamic SQL or OPTION RECOMPILE to get tailored execution plans.

This
>What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

Is simple and not too relevant. In the sample you posted if b.guid is null, then the query returns every row in Party.