November 16, 2009 at 6:35 am
Comments posted to this topic are about the item Introduction to Indexes: Part 3 – The nonclustered index
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
November 18, 2009 at 6:29 am
Great series Gail. Glad you mentioned the index intersection in this article, I've always had questions about that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2009 at 6:53 am
November 18, 2009 at 6:53 am
Asking what is probably an obvious question, but from what I understand:
Adding a clustered index that covers multiple columns within a table is not necessarily advantageous, because it will expand all of the non-clustered indexes as well. (from part II)
Therefore, the clustered index should be as narrow as possible, and the non-clustered index should be created as a composite key to assist with querying?
So if I have a heap that only has a unique value by combining three separate columns, I should create a new column with a unique value (using newsequentialid() or some such) and make that the clustered index, but use the composite non-clustered index to help my queries against the table?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 18, 2009 at 7:18 am
jcrawf02 (11/18/2009)
Adding a clustered index that covers multiple columns within a table is not necessarily advantageous, because it will expand all of the non-clustered indexes as well. (from part II)
Yes
Therefore, the clustered index should be as narrow as possible, and the non-clustered index should be created as a composite key to assist with querying?
Yes.
So if I have a heap that only has a unique value by combining three separate columns, I should create a new column with a unique value (using newsequentialid() or some such) and make that the clustered index, but use the composite non-clustered index to help my queries against the table?
Maybe.
Remember that clustered index != primary key. While it is a good idea to put the cluster on a unique column, it's not obligatory. SQL can make the clustered index unique (by adding an int behind the scenes) if the column it's defined on is not. Look at what columns exist. Are any of them fairly good candidates for the cluster? Are any reasonably good?
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
November 18, 2009 at 7:22 am
ok, so if I have a system generated UID from a different table, but the values are not entirely unique, only a few duplicates in my heap, I could/should use that, as the system would make the clustered index unique in the backend? That was my first instinct, but got derailed when I saw duplicates.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 18, 2009 at 7:33 am
jcrawf02 (11/18/2009)
ok, so if I have a system generated UID from a different table, but the values are not entirely unique, only a few duplicates in my heap, I could/should use that, as the system would make the clustered index unique in the backend? That was my first instinct, but got derailed when I saw duplicates.
Maybe. How well does it fit the other guidelines for the clustered index? Narrow, unchanging, ever-increasing? I've often used a 'date inserted' type column for the cluster. Not quite unique, fairly narrow, unchanging, ever-increasing. It's all about what tradeoffs you're willing to make.
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
November 18, 2009 at 7:38 am
Thanks, food for thought. Narrow (15 character static IDs), unchanging (check), ever-increasing (not sure, ID has leading alpha characters and is not consistently assigned). In short, not sure that's my best choice, but I'll go back and re-evaluate.
Great series, I'll be reading and re-reading these for a long time. 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 18, 2009 at 9:42 am
Great article - All three parts.
In the column order I get that as this is how the index is sorted. When talking about included columns one would guess that the order is no more or less important than the column order in the table itself.
ATBCharles Kincaid
November 18, 2009 at 11:49 am
I agree, a great series of articles explained in a way that is easy to understand. Gail, thanks for taking the time to put this together.
November 18, 2009 at 12:37 pm
Charles Kincaid (11/18/2009)
When talking about included columns one would guess that the order is no more or less important than the column order in the table itself.
Indeed. Order for included columns is irrelevant, they're only at the leaf level, they cannot be used for seeks. Just the same as the non-key columns for a clustered index.
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
November 18, 2009 at 1:39 pm
Great series Gail.
Wondering if you had any thoughts about this: Say I have a non-unique, non-clustered index with some included columns. It covers a bunch of frequently used queries. I could make it an unique index if I add another column to the index, assuming unique indexes are better than non-unique ones. Without more info, what's your instinct: add the column to make the index unique or don't add more than necessary. And, what if that column also happens to be part of the clustered index for that the underlying table? Would that alter your recommendation?
November 18, 2009 at 2:12 pm
Good question which reminds me of something similar. The Tuning Adviser often recommends that a column which is also a part of the Clustered index be added as an included column. I do not do this since I assume that it is included in the index anyway. Would including such a column make the index larger for no apparent gain?
November 18, 2009 at 2:53 pm
November 18, 2009 at 3:19 pm
Great read! Well written and informative.
Viewing 15 posts - 1 through 15 (of 92 total)
You must be logged in to reply to this topic. Login to reply