14

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: Execution Plan

10

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;
  • Very interesting, wouldn't have thought of this. It does seem like you can just get rid of the COALESCE at this point though; I believe the CASE statement was already guaranteed to return 0 or 1, but ISNULL was present only so SQL Server would yield a non-nullable BIT for the computed column. However, COALESCE will still yield a nullable column. So the one impact of this change, with or without the COALESCE, is that the computed column is now nullable but the index seek can be used. – Geoff Patterson Dec 16 '15 at 21:36
  • @Geoff Yes, that's true. But in this case since we know by the computed column definition NULL is really not a possible output, this only really matters if we're using this table as the source of a SELECT INTO. – Aaron Bertrand Dec 16 '15 at 21:37
  • This is some amazing information -- thank you! My end goal is to that the DataA and DataB columns are used as "dirty" uuids to allow for asynchronous updating of denormalized columns on the record, so there shouldn't be too many where the Diff flag is 1. If I use the static field, then I was thinking of adding a trigger to monitor the two uuids and update the field. – David Faivre Dec 16 '15 at 21:51
  • Also, as @GeoffPatterson pointed out, can I not use the COALESCE? Why would I keep it? – David Faivre Dec 16 '15 at 21:59
  • @David You can probably drop the COALESCE. I tried to maintain the look and intent of your original code, and didn't test without it, so that testing will be on you. (I can't explain why you had ISNULL there in the first place, either.) – Aaron Bertrand Dec 16 '15 at 22:00
5

This is a specific limitation of the SQL Server computed column matching logic, when an outermost ISNULL is used, and the datatype of the column is bit.

Bug report

To avoid the issue, any of the following workarounds may be employed:

  1. Do not use an outermost ISNULL (the only way to make a computed column NOT NULL).
  2. Do not use the bit data type as the final type of the computed column.
  3. Make the computed column PERSISTED and enable trace flag 174.

Details

The heart of the issue is that without trace flag 174, all computed column references in a query (even persisted) are always expanded into the underlying definition very early in query compilation.

The idea of expansion is that it could enable simplifications and rewrites that can only work on the definition, not on the column name alone. For example, there may be predicates in the query referencing that computed column that could make part of the calculation redundant, or otherwise more constrained.

Once early simplifications and rewrites are considered, query compilation attempts to match expressions in the query to computed columns (all computed columns, not only those originally found in the query text).

Unchanged computed column expressions match back to the original computed column without issue in most cases. There appears to be a bug when specific to matching an expression of bit type, with an outermost ISNULL. Matching is unsuccessful in this specific case, even where a detailed examination of the internals shows that it should succeed.

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.