We help IT Professionals succeed at work.

QUERY TUNE

Medium Priority
590 Views
Last Modified: 2012-08-22
Hi,

Is their better way formatting this query.
It does a clustered scan instead of seek
SELECT * FROM tablename b WHERE LEFT(b.id, 1) = 'y'
AND CASE WHEN SUBSTRING(b.id, 5, 8) NOT LIKE '%[a-Z]%'
THEN CONVERT(INT, SUBSTRING(b.id, 5, 8))
ELSE NULL END = '13385' OR former_id = 'Z13385'

Please advise
Comment
Watch Question

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
Try

SELECT *
FROM tablename b
WHERE b.id LIKE 'y____13385%' OR former_id = 'Z13385'
CERTIFIED EXPERT
Top Expert 2012

Commented:
As pointed out if you need a leading wildcard (SUBSTRING(b.id, 5, 8) NOT LIKE '%[a-Z]%' ) than you are going to have a table scan and there is not much you can do about that.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
lwadwell's code is best.

But the "OR" could still force a scan here ... unless there is are indexes on id AND former_id AND both values are selective enough that SQL tries to merge the index results rather than just scanning the table.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.