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
```cs
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 Nonclustered 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 use the newly-created index and yields the same index seek plan.
Three questions:
1. Why does the filtered index have such poor performance in this case?
2. How can I make Linq omit the `IS NULL` part?
3. Is there a way to enforce a unique constraint that allows `NULL` with just one non-filtered index?
TopAnswers
Databases
Top Answer
Josh Darnell (imported from SE)
> Why does the filtered index have 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 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 a unique constraint 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.