LIKE '501%' VS LEFT(ACCOUNT, 3)

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

Transact-SQL (2000)

LIKE '501%' VS LEFT(ACCOUNT, 3)

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2006-03-28 : 12:39:28

I think I know the answer to this question, but I need to ask. Which has better performance.

SELECT *
FROM PB_JOBCOST
WHERE LEFT(ACCOUNT,3) = '501';

or

SELECT *
FROM PB_JOBCOST
WHERE ACCOUNT LIKE '501%'

Thanks,
Doug

jhermiz

3564 Posts

Posted - 2006-03-28 : 13:04:26

LIKE probably, stay away from SELECT *.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2006-03-28 : 13:09:27

WOW! Really, I figured the left(account,3)='501' would be faster. I did not think the wildcard operators used the index. Sorry about the Select *, I was using that just for the example.

Thanks,
Doug

jhermiz

3564 Posts

Posted - 2006-03-28 : 13:17:46

Well you never said there was an index on account!! DDL always helps!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 13:42:17

The first example can't use an index as you haven't isolated the column to one side of the predicate. Since you are using the starts with of LIKE, it can use an index (as opposed to %501% which won't).

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources