none
cluster index structure

    Question

  • Hi,

    Index is stored separtely than the table itself.

    How about Cluster index whn the leaf level of this index is data itself?

    Wednesday, November 14, 2012 5:43 PM

Answers

All replies

  • Hello,

    For details please have a look at MSDN: Clustered Index Structures


    Olaf Helper

    Blog Xing

    Wednesday, November 14, 2012 5:52 PM
  • Yes, if a table has a clustered index, the data is stored in the leaf level of the index, so the table and the index are the same structure and stored together.  It is only nonclustered indexes that are separate structures from the data.

    Tom

    Wednesday, November 14, 2012 5:54 PM
  • So can we say CI and table is one thing which also means CI is bigger in size than table?

    Also when you have index and data stored on separate drives, then CI still stays with data drive and only NCI are in other drive?

    Wednesday, November 14, 2012 6:10 PM
  • then CI still stays with data drive and only NCI are in other drive?

    That's a restriction, a CI is always stored in the same file group as the table. Only NCI can be stored in a different file group then the table.

    But a CI isn't bigger, it has only a different structure then a NCI.


    Olaf Helper

    Blog Xing

    Wednesday, November 14, 2012 6:18 PM
  • What is mean by

    "clustered index determines the logical order in which table data is stored"?

    I thought CI determines physical order of table and it arrange data pages everytime there is a Insert in table?

    Wednesday, November 14, 2012 6:51 PM
  • The logical is the physical order, definded by the clustered index and it ORDER definition.

    Olaf Helper

    Blog Xing

    Wednesday, November 14, 2012 6:57 PM
  • Hi Me.Saqib

    For a clustered index we can say that the index itself becomes the table. In this sense we can see that when we insert any data into a table the insert will be dependent on the ordering of the index and the other rows.

    • For a regular table if we inserted a 1 then a 3 and then a 2. We would get a table which was ordered 1,3,2.
    • A clustered index order ascending would not care in which order the row was inserted. In the case above we would get a table which was ordered 1,2,3.
    • A clustered index order descending would not care in which order the row was inserted. In the case above we would get a table which was ordered 3,2,1.

    The Logical part from your question above has more to do with the indexes be tree structure. This structure is organized based on table order and data to allow for the quickest retrieval. When searching say for the numbers 50-59. The clustered index being ordered sequentially would allow it to be traversed in order. Essentially jumping to the number 50 and ending at the 59. The results are returned faster because the RDBMS is aware that it no longer has to search in other areas since all the numbers we wish to be returned are stored in order.

    On another note fragmentation would be when there is a difference between these two layers.


    Pérez


    • Edited by Peréz Wednesday, November 14, 2012 7:47 PM corrections
    Wednesday, November 14, 2012 7:19 PM
  • Cluster Index is one by one with your primary key. One key one leaf. So this is 1-1 index. A Non cluster index may have many leafs per key.

    cluster index sql server 2008


    OR Vs XOR

    Wednesday, November 14, 2012 8:10 PM