Best practice when to use an index Part II

Question

Post reply

Best practice when to use an index Part II

Frank Kalis

SSC Guru

Points: 111183
More actions
July 15, 2003 at 4:22 am

#64788

Hi there,
this is somehow a follow-up to the above mentioned thread.
Consider you have a table, several indexes properly designed and now you want to run some queries.
1. SELECT blala FROM table WHERE field != value
Is in this case any index used, or does SQL Server always perform a table scan because of the != operator?
2. SELECT blabla FROM table WHERE DAY(field) = somevalue
How does using the function DAY() affect the use of indexes?
3. SELECT blabla FROM table WHERE field LIKE '%xyz'
Is it true, that is this case an index is NOT used, while ...LIKE 'xyz%' uses an index? If so, why?
To index or not to index....
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 18, 2003 at 12:00 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 1

1. no index used, full scan required.

2. no index used with functions

3. no index used

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi Steve,

quote:
1. no index used, full scan required.
2. no index used with functions
3. no index used

thanks for responding!

Any rationale for me, or a hint where to search further?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 3

quote:
1. SELECT blala FROM table WHERE field != value
Is in this case any index used, or does SQL Server always perform a table scan because of the != operator?
no index used, full scan required.
2. SELECT blabla FROM table WHERE DAY(field) = somevalue
How does using the function DAY() affect the use of indexes?
no index used with functions
3. SELECT blabla FROM table WHERE field LIKE '%xyz'
Is it true, that is this case an index is NOT used, while ...LIKE 'xyz%' uses an index? If so, why?
no index used

It is kind of the same reasoning as a bit field not needing an index.

It is assumed the majority of the values are != then it will have to read the tables data anyway.

Instead SQL reads the table and discard what isn't needed.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi James,

quote:
It is kind of the same reasoning as a bit field not needing an index.
It is assumed the majority of the values are != then it will have to read the tables data anyway.
Instead SQL reads the table and discard what isn't needed.

yes, that was the easy one. What about my third question?

Is it because in wildcard searches with % that all characters are significant, and because I'm searching for anything with ...and then xyz that all rows must be compared?

Basically I'm looking for an explanation for a 'normal' user, not that scientific stuff which is sometimes hard to follow. And even harder to explain to that 'normal' user.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]