Why case..when get a table scan ? how to workarround-CodePudding

When I use CASE .. WHEN .. END I get an index scan less efficient than the index seek.

I have complex business rules I need to use the CASE, is there any workaround ?

Query A:

    select * from [dbo].[Mobile]
    where((
    CASE 
    where ([MobileNumber] = (LTRIM(RTRIM('987654321'))))
    END
    ) = 1)

This query gets an index scan and 199 logical reads.

Query B:

    select * from [dbo].[Mobile]
    where ([MobileNumber] = (LTRIM(RTRIM('987654321'))))

This query gets an index seek and 122 logical reads.

CodePudding user response：

For the table

CREATE TABLE #T(X CHAR(1) PRIMARY KEY);

And the query

SELECT *
FROM   #T
WHERE  CASE WHEN X = 'A' THEN 1 ELSE 0 END = 1;

It is apparent without that much thought that the only circumstances in which the CASE expression evaluates to 1 are when X = 'A' and that the query has the same semantics as

SELECT *
FROM   #T
WHERE  X = 'A';

However the first query will get a scan and the second one a seek.

The SQL Server optimiser will try all sorts of relational transformations on queries but will not even attempt to rearrange expressions such as CASE WHEN X = 'A' THEN 1 ELSE 0 END = 1 to express it as an X = expression so it can perform an index seek on it.

It is up to the query writer to write their queries in such a way that they are sargable.

There is no workaround to get an index seek on column MobileNumber with your existing CASE predicate. You just need to express the condition differently (as in your example B).

Potentially you could create a computed column with the CASE expression and index that - and you could then see an index seek on the new column. However this is unlikely to be useful to you as I assume in reality the mobile number 987654321 is dynamic and not something to be hardcoded into a column used by an index.

CodePudding user response：

After cleaning up and fixing your code, you have a WHERE which is boolean expression based around a CASE.

As mentioned by @MartinSmith, there is simply no way SQL Server will re-arrange this. It does not do the kind of dynamic slicing that would allow it to re-arrange the first query into the second version.

select *
from [dbo].[Mobile]
where
    CASE 
    WHEN [MobileNumber] = LTRIM(RTRIM('987654321'))
    THEN 1
    END
 = 1

You may ask: the second version also has an expression in it, why does this not also get a scan?

select *
from [dbo].[Mobile]
where [MobileNumber] = LTRIM(RTRIM('987654321'))

The reason is that what SQL Server can recognize is that LTRIM(RTRIM('987654321')) is a deterministic constant expression: it does not change depending on runtime settings, nor on the result of in-row calculations.

Therefore, it can optimize by calculating it at compile time. The query therefore becomes this under the hood, which can be used against an index on MobileNumber.

select *
from [dbo].[Mobile]
where [MobileNumber] = '987654321'