• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

QUERY TUNE

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
0
Sonali P
Asked:
Sonali P
1 Solution
 
lwadwellCommented:
Try

SELECT *
FROM tablename b
WHERE b.id LIKE 'y____13385%' OR former_id = 'Z13385'
0
 
Anthony PerkinsCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now