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?
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.