1

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:

  1. Why does filtered index has such poor performance in this case?
  2. How can I make linq to omit the IS NULL part?
  3. Is there a way to enforce unique constrain that allows NULL with just one non filtered index?
2

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.

2
  • just curious. why is the planner not short circuiting the second clause when it sees the value of @V is not null?
    – Steve
    Mar 6 '20 at 21:53
  • @Steve you might see that behavior if you add OPTION (RECOMPILE) to the end of the query. Generally the optimizer needs to build an execution plan that is "safe" for any parameter, since it caches and reuses that plan. So you won't see short circuiting like that. Mar 6 '20 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.