Index Design

  • Hi All

    After reading a few articles on Clustered/NonClustered Index design, I want to comfirm my understanding on the topic and make sure I'm not missing anything.

    Clustered: The leaf level of the Clustered index is the actual data rows of the table, sorted Logically in a way that makes it easy for SQL Server to find.

    NonClustered: The leaf leaf level of the Nonclustered index contains the data rows of the Column(s) specified in the Index definition, along with that, it also contains a pointer to the where the rest of the row resides. It also keeps the data rows of the Clustering key(The column(s) specified in the Clustered Index definition).

    Am I missing anything here?

    Thanks

  • What you have written about NonClustered Index in confusing to me. So instead of commenting on that, I would ask you a question to encourage to do further research on this topic:

    What does NonClustered Index contain if the table is a heap?

  • SQLSACT (8/24/2012)


    Am I missing anything here?

    Mainly that the pointer to the rest of the row is the clustered index key (if the base table is a clustered index). Not two separate things.

    Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level.

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    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 (8/24/2012)


    SQLSACT (8/24/2012)


    Am I missing anything here?

    Mainly that the pointer to the rest of the row is the clustered index key (if the base table is a clustered index). Not two separate things.

    Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level.

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Thanks

    Help me understand something regarding the Leaf Level of the nonclustered Index

    Consider the following

    --Table

    create table test

    (Col1 int primary key clustered

    , Col2 int

    , Col3 int)

    --Index

    create nonclustered index NCX on test

    (Col3)

    --Query

    select Col3 from test where Col3 = '50'

    Would my select statement still have an interaction with the base table even though the nonclustered index satisfies the query completely?

    Thanks

  • If the index satisfies the query completely, why would SQL need to go to the base table?

    Please read the articles I referenced.

    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
  • SQLSACT (8/24/2012)


    Would my select statement still have an interaction with the base table even though the nonclustered index satisfies the query completely?

    No. Such indexes are called Covering Indexes.

  • GilaMonster (8/24/2012)


    If the index satisfies the query completely, why would SQL need to go to the base table?

    Please read the articles I referenced.

    I'm confused about your first reply

    Also, indexes (nonclustered) don't contain data rows, they contain index rows. Only the clustered index has data pages as the leaf level

    If the index satisfies the query completely and a NonClustered index doesn't contain the data rows at its leaf level. Surely then it must go to the base table?

    Thanks

  • No, what would it need to go to the base table for?

    The only column you're referencing in your query in Col3, and that's in the index row at the leaf of the nonclustered index.

    Please read the articles I referenced.

    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 (8/24/2012)


    No, what would it need to go to the base table for?

    The only column you're referencing in your query in Col3, and that's in the index row at the leaf of the nonclustered index.

    Please read the articles I referenced.

    Thanks

    I think I'm getting lost in the jargon with this topic

    Clustered Index Leaf level - Contains data rows

    NonClustered Index Leaf level - Contains Index rows

    I'm struggling to understand the difference between the Data Rows and Index Row

    Thanks

  • Data row = that which is found in a table (heap or clustered index)

    Index row = that which is found in nonclustered indexes.

    There are some differences in their details, but that's not important.

    You say "Data row" and people will think you mean a row with all the columns that the table has. That is not what is in nonclustered indexes. They have just the index key, the pointer to the data row and any include columns.

    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 (8/24/2012)


    Data row = that which is found in a table (heap or clustered index)

    Index row = that which is found in nonclustered indexes.

    There are some differences in their details, but that's not important.

    You say "Data row" and people will think you mean a row with all the columns that the table has. That is not what is in nonclustered indexes. They have just the index key, the pointer to the data row and any include columns.

    Thanks - The Data/Index row is what had me

    the pointer to the data row

    This pointer is what is used when the Index doesn't cover the query and a Lookup to the Table/Clustered Index is required, right?

    Thanks

  • Yes, it's used to find the data row.

    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 (8/24/2012)


    Yes, it's used to find the data row.

    Thanks Gail

  • GilaMonster (8/24/2012)


    Yes, it's used to find the data row.

    Thanks

    I wanna run something by you that might be out of the scope of this thread, please just let me know.

    I came across an article of yours on Simple-Talk

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    I want to make sure that I understand something correctly

    If we have an Index that doesn't full satisfy a query, a Lookup to the Clustered Index/Table will then be done, ok.

    Am I right in that this isn't always the case. Given that a Lookup can be expensive, SQL Server will first evaluate the data and will choose to do a Clustered Index/Table scan instead, under these circumstances:

    >> Too many rows are being returned

    >> The data is not unique enough

    Are there any other circumstances where SQL Server will choose to do a Clustered Index/Table scan instead.

    Thanks

  • Have you read those three indexing articles yet? If not, please read them, especially the third one.

    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 - 1 through 15 (of 15 total)

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