question

lumiga avatar image
lumiga asked

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

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.

tsqlsql server 2017
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

There's a typo in your CASE query, but I assume you meant to set the value to 1, then compare it to 1.

The reason you get a scan for Query A is that you are effectively asking for all the rows where {some condition} = 1. SQL will have to evaluate all rows to see what the result of {some condition} is.

QueryB is presumably able to use an index keyed on MobileNumber, hence the seek.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.