many like statements with the same field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

many like statements with the same field

Author

Topic

cardgunner

326 Posts

Posted - 2008-09-30 : 15:52:18

How can I shorten this statements


where 	a.catg like 'AE%' or 
	a.catg like 'AT%' or 
	a.catg like 'ME%' or 
	a.catg like 'MT%'

I'd like to use

where a.catg like in ('AT%', 'AE%', 'ME%', 'MT%')

but I get an error

Incorrect syntax near the keyword 'in'

Thanks in advance.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 15:56:30

where SUBSTRING(a.catg, 1, 2) in ('AT', 'AE', 'ME', 'MT')

E 12°55'05.63"
N 56°04'39.26"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 15:59:30

Compare the execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cardgunner

326 Posts

Posted - 2008-09-30 : 16:02:52

quote:
Originally posted by Peso

where SUBSTRING(a.catg, 1, 2) in ('AT', 'AE', 'ME', 'MT')

Yes that will work, perfectly. Thank you.

I didn't think of doing it that way, but I not really thinking today anyways.

I'll blame it on the Steelers and the thirst from those chicken wings.

Thanks again.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 16:09:23

[code]CREATE TABLE #Sample
(
catG VARCHAR(20)
)

INSERT #Sample
SELECT CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
FROM master..spt_values
WHERE type = 'P'

CREATE NONCLUSTERED INDEX IX_Sample ON #Sample (catG)

-- Original -> Index seek
SELECT *
FROM #Sample
WHERE catg like 'AE%' or
catg like 'AT%' or
catg like 'ME%' or
catg like 'MT%'

-- Peso 1 -> Index scan
SELECT *
FROM #Sample
WHERE SUBSTRING(catg, 1, 2) in ('AT', 'AE', 'ME', 'MT')

-- Peso 2 -> Index seek
SELECT *
FROM #Sample
WHERE catg LIKE '[am][et]%'

DROP TABLE #Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

cardgunner

326 Posts

Posted - 2008-09-30 : 16:10:06

quote:
Originally posted by tkizer

Compare the execution plans.

I'm interested in what this means.

I'm using SQL 2000 analyzer.

I hit the execution plan button on the tool bar.

Both ways, my long way and Peso's short way, both run in 0 secs. (Only 770 records)

What else am I looking for in the plan to test?

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 16:11:59

My previous post.
Tara was referring to the difference between an INDEX SCAN vs INDEX SEEK.

It might not be an performance issue, because I doubt you showed us the full query.
Maybe you have more WHERE clauses? Maybe you are SELECTing columns not part of an index anyway, and thus having BOOKMARK LOOKUP in the execution plan.

Until you give us the full picture, all we can do is to give educated guesses based on some intuition.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources