Given the following table
CREATE TABLE Test( Id INT PRIMARY KEY IDENTITY(1, 1), Val INT ) CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL DECLARE @i INT = 0 WHILE (@i < 30000) BEGIN INSERT INTO Test(Val) VALUES(@i) SET @i = @i + 1 END
and I have a linq compiled query from a statement similar to
Test.Where(x => x.Val == 123).FirstOrDefault();
this gets compiled to something similar to
DECLARE @V INT SELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL)
Seems legit. But I am very disappointed when seeing the clustered index scan in the query plan.
The plan looks way better when I remove the second clause.
SELECT TOP 1 Id FROM Test WHERE Val = @V
This time the plan is a non clustered Index Seek with 1 row read
So my first guess is that the filtered index is the evil here and I created a second non-clustered not unique index on the same column
CREATE INDEX IX_Test_Val ON Test(Val)
This time both statements uses the newly created index and yields to the same index seek plan.
Here comes three questions:
- Why does filtered index has such poor performance in this case?
- How can I make linq to omit the IS NULL part?
- Is there a way to enforce unique constrain that allows NULL with just one non filtered index?