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.
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 =
.
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.
Moral of the story
Avoid using CASE expression in the where clause.