Computed Column Index Not Used

I want to have a fast lookup based on if two columns are equal. I tried to use a computed column with an index, but SQL Server doesn't seem to use it. If I just use a statically populated bit column with an index, I get the expected index seek.

Seems there is some other questions like this out there, but none focused on why an index wouldn't be used.

Test Table:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

And the Query:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

And the resulting execution plans:

Try with COALESCE instead of ISNULL. With ISNULL, SQL Server doesn't seem capable of pushing a predicate against the narrower index, and therefore has to scan the clustered to find the information.

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS COALESCE(convert(bit, case when [DataA] is null 
      and [DataB] is not null then 1 when [DataA] <> [DataB] 
      then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    );

That said, if you stick with a static column, a filtered index might make more sense, and will have lower I/O costs (all depending on how many rows typically match the filter predicate) e.g.:

CREATE INDEX ix_DiffStaticFiltered 
  ON dbo.Diffs(DiffStatic)
  WHERE DiffStatic = 1;

StackExchange Database Administrators Q#123863, answer score: 11