SQL Server LINQ – Compiled Query Uses Clustered Index Instead of Filtered Index

linqsql server

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?

Best Answer

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.

Related Question