Introduction to Indexes: Part 3 – The nonclustered index

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Great series Gail. Glad you mentioned the index intersection in this article, I've always had questions about that.

  • Thanks Gail,

    Well written. I read all 3 and learned a few things.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • 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."

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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."

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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."

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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?

  • this makes it so much easier to understand how it works. thanks!

    DJ[/url]

  • 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