Is CASE..WHEN..THEN sargable ?

Question

Hi!
The big question is case sargable ?
If not is there any workaround to make it sargable ?

Query A:

select * from [dbo].[Mobile]  
where((  
CASE   
When ([MobileNumber] = '123456789') THEN cast(1 as bit)  
END  
) = 1)   

I'm doing case in where statement because I have complex business logic.
This query gets an index scan and 199 logical reads. It looks that is not sargable.

Query B:

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

This query gets an index seek and 122 logical reads it looks sargable.

Answer 1

Correct. CASE expressions are not sargable. Very few expressions where an indexed column is entangled of in an expression of any sort can lead to Index Seek.

Answer 2

Hi @Miguel Gavinhos

Like erland said，SQL Server is very strict about using Index Seek.
Even below code also can't lead to Index Seek.

SELECT ... FROM ...  
WHERE Year(myDate) = 2008  

Reference thread: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable

A Search ARgument ABLE predicate is one where SQL SERVER can utilize an index seek operation, if an index exists.

A SARGable predicate is one where SQL server can isolate the single value or range of index key values to process

SARGable predicates include the following operators: =, >, >=, <, <=, IN, BETWEEN, and LIKE (in the case of prefix matching)

Non-SARGable operators include: NOT, NOT IN, <>, and LIKE (not prefix matching), as well as the use of functions or calculations against the table, and type conversions where the datatype does not fulfill the index created.

Reference thread:https://dba.stackexchange.com/questions/162263/what-does-the-word-sargable-really-mean

Best Regards,
Isabella