Linq compiled query uses clusted index instead of filtered index
Why does filtered index has such poor performance in this case?


The query couldn't use the filtered index, because of the IS NULL part.  By definition, the query needs to consider rows that have a Val of NULL, and the filtered index doesn't have those rows.


  How can I make linq to omit the IS NULL part?


This isn't really a database question, but...

LINQ is accounting for your parameter being nullable.  In the example you gave, it's not nullable (it's an integer literal).  In your real code, it's probably a nullable int (int?) or a string, or some other reference type.  Change the parameter to be non-nullable (or if it's a nullable value type, call .Value) to remove the IS NULL part of the query.


  Is there a way to enforce unique constrain that allows NULL with just one non filtered index?


If I understand your question, then no - I don't think so.  A UNIQUE constraint or index will allow 1 NULL value, but not multiple.  However, the filtered index might work for your purposes once the query issue has been fixed.