Introduction to Indexes

  • A phone book analogy is often used here. You have a list of people's names in a phone book. The root would be analogous to the first letter of their last names. The intermediate would be subgroups of that, so, for example, Smith to Styles (but not their individual values), and the leaf would be the individual entries (each person's name (and telephone number)).

    (Changed my avatar from a Husky to Carl from the movie UP! for the new year. :-))

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • Yes! Now it makes sense. I couldn't grasp the Intermediate Level and it's use.

    Danke!

  • vj2173 (1/28/2011)


    From your diagram, am I to assume that the root page contain the indexed column and all of the values from the index column. From

    1 to 137?

    No. Just 1 and 137. It doesn't need any values between those, the root only stores the starting value for each page beneath it

    Secondly, the Intermediate pages (level) hold segments of those values from the indexed column?

    Again, no. The intermediate pages in that diagram contain only the values specified. Like the root, the lowest key value for each page beneath it.

    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
  • Clustered indexes define the logical order of the table

    I would say: Clustered indexes define the physical order of the table. Am I right?

  • e-ghetto (7/3/2012)


    Clustered indexes define the logical order of the table

    I would say: Clustered indexes define the physical order of the table. Am I right?

    No. There is no guarantee of physical order whatsoever. It's the logical order.

  • e-ghetto (7/3/2012)


    Clustered indexes define the logical order of the table

    I would say: Clustered indexes define the physical order of the table. Am I right?

    No. They define the logical order only.

    If a clustered index defined and guaranteed the physical order of data on disk, then clustered indexes would never become fragmented (as fragmentation is defined as the difference between physical and logical order). Is that the case?

    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
  • Physical order when index is built or rebuild, logical order thereafter until next rebuild of the cluster index. Kimberly tripp has nice articles on this and this topic is discussed in the master training vids. Also you can verify it by using a function called %%physloc%% which will show you the location on disk. So if you look at the table it will show you how the structure is stored on disk and you will notice it follows consecutively. BUt that only happens when the cluster index is built or rebuilt.

  • It's not even guaranteed after a create or rebuild. Try rebuilding an index and checking the fragmentation. If it's not exactly 0 then that freshly built/rebuilt index is still not in exact physical order.

    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
  • GilaMonster (7/3/2012)


    No. They define the logical order only.

    Interesting! I'm in the business since five years - and all the time I've been wrong!:w00t:

    Thanks!

  • GilaMonster (7/3/2012)


    No. They define the logical order only.

    The first time that you JOIN it won't matter anyway. There may be some ordering in intermediate result sets but the only thing getting benefit from that is the query processor and not you. Any ordering that you see in output results are happenstance. If you want it sorted use ORDER BY rather than relying on indexes.

    By the way, I've been at this for over 30 years. There are days when I'm wrong. Not many but they happen.

    ATBCharles Kincaid

  • Charles Kincaid (7/3/2012)


    The first time that you JOIN it won't matter anyway.

    Any time at all that you query the index's order is irrelevant. Join or no join.

    I'm just sick of the myth that indexes enforce a physical order. It makes it hard to understand fragmentation and other aspects of index and query behaviour.

    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
  • GilaMonster (7/3/2012)


    Any time at all that you query the index's order is irrelevant. Join or no join.

    Sorry. I misspoke (mistyped, whatever). In the absence of ORDER BY it’s happenstance what order the rows come back in. I have seen the same query, not having ORDER BY, produce different ordering and not even the underlying data changed.

    ATBCharles Kincaid

  • Charles Kincaid (7/3/2012)


    In the absence of ORDER BY it’s happenstance what order the rows come back in. I have seen the same query, not having ORDER BY, produce different ordering and not even the underlying data changed.

    Agreed. Nothing I've said in this article or elsewhere claims otherwise.

    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
  • GilaMonster (7/3/2012)


    Any time at all that you query the index's order is irrelevant. Join or no join.

    Hmm, when is the index's order relevant? I can create an index in ASC or DESC order:

    CREATE NONCLUSTERED INDEX IX_PurchaseID ON dbo.Purchase(

    PurchaseID DESC --descending order!

    );

    Will this "DESC" on PurchaseID make my query (potentially) faster if I want to select the latest purchase instead of the first?

  • e-ghetto (7/3/2012)


    GilaMonster (7/3/2012)


    Any time at all that you query the index's order is irrelevant. Join or no join.

    Hmm, when is the index's order relevant?

    For seeking, for range scans, for supporting an order by. Not ever for returning rows in the index order without an order by specified. That was quoted a bit out of context (context being ordering of returned rows without an order by)

    Will this "DESC" on PurchaseID make my query (potentially) faster if I want to select the latest purchase instead of the first?

    I highly doubt it.

    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

Viewing 15 posts - 106 through 120 (of 125 total)

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