sql-serverindexingsql-server-2008-r2database-performance

CASE WHEN index usage in SQL Server: seek vs scan


Given the following schema:

create table dbo.SomeTable 
(
    ID int primary key identity(1, 1),
    SomeColumn varchar(50)
);

create index IX_SomeColumn on dbo.SomeTable (SomeColumn);

And populate it with some data:

declare @i int = 1000;

while @i > 0 begin
    set @i = @i - 1;

    insert dbo.SomeTable (SomeColumn) 
    values (convert(varchar, @i))
end

This query performs an index seek:

select ID 
from dbo.SomeTable 
where SomeColumn = '431'

While this query performs an index scan:

select ID 
from dbo.SomeTable 
where case when SomeColumn = '431' then 1 else 0 end = 1

Is there a way to make the latter (or something equivalent) perform an index seek instead?

I'm asking because I want to be able to put the case when into the select list of a view and use it in a where clause, but it will never perform as well as the raw form if I can't get SQL Server to do an index seek.


Solution

  • If you look at the execution plan for both queries the seek predicate is very different.

    When you do not use a CASE expression in the where clause, the seek predicate leaves the Column alone (does not have to do any calculations on column values) and just do a seek on the index to find the value on the other side of the =.

    enter image description here

    On the other hand when you use a CASE expression in the where clause, things change a lot. Now the seek predicate has parameterized the where clause on both sides of the Where expression. In simple words SQL server does not know what value will be yield for CASE WHEN [TEST_DB].[dbo].[SomeTable].[SomeColumn] = [@1] THEN [@2] ELSE [@3] END = CONVERT_IMPLICIT(int,[@4],0) until it has actually been executed hence using the available indexes option goes out of the window and SQL server ends up doing a scan.

    enter image description here

    Moral of the story

    Avoid using CASE expression in the where clause.