March 26, 2011 at 12:01 pm
Hi all. Studying for 70-433 exam. Book states (in the where clause explanation: "For indexes to be utilized when an OR operator is specified, all columns referenced by the OR condition must be included in an index or none of the indexes are used."
I take it by the plural at the end that for n colums connected with the or, there should be at least n indexes.
a) Any benefit to have a single index with all the columns (if the combination is used often enough)?
b) Does a covering index provide the same benefit?
c) Any downside to 1 or more of the indecies having additional columns?
TIA!
March 26, 2011 at 12:18 pm
alan.berger (3/26/2011)
I take it by the plural at the end that for n colums connected with the or, there should be at least n indexes.
Yup.
Shameless plug - am busy writing a blog post on this very subject
a) Any benefit to have a single index with all the columns (if the combination is used often enough)?
None whatsoever. It'll index scan. It has to be one index for each column in the OR
b) Does a covering index provide the same benefit?
See above. Having them all covering provides a benefit. Having only one, not so much.
c) Any downside to 1 or more of the indecies having additional columns?
Nope, providing the columns are in the right order for the query to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2011 at 12:23 pm
Awesome. Please let me know when the blog is posted. I'd appreciate a link sent to alanb.dba@gmail.com
Thanks!!
(and I'm all for shameless plugs!)
March 26, 2011 at 12:29 pm
alan.berger (3/26/2011)
Awesome. Please let me know when the blog is posted. I'd appreciate a link sent to alanb.dba@gmail.com
Feel free to subscribe to my blog. I haven't even finished writing the post yet, probably won't be published til mid-April.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply