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