CASE WHEN index usage in SQL Server: seek vs scan

Question

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.

Answer 1

The only way you could get a seek there would be by making the case when SomeColumn = '431' then 1 else 0 end expression a computed column and indexing the computed column.

You should then find that the expression is matched to the computed column allowing a seek (at the expense of maintaining an extra index).

(If you encounter problems with auto parameterisation preventing a match adding a redundant 1=1 will prevent this. SQL Fiddle with plan showing a seek)