Best practice when to use an index Part II
-
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] -
This was removed by the editor as SPAM
-
1. no index used, full scan required.
2. no index used with functions
3. no index used
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
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] -
quote:
1. SELECT blala FROM table WHERE field != valueIs 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.
-
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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply