none
difference between cluster index and noncluster index

    Question

  • hi
    
    
    clustered index is physically stored 
    a table can have 1 clustered index
    
    non clustered index is logically stored 
    a table can have 249 non clustred index

    how cluster index physically store a table and how noncluster index logically store a table

    thanks in advance

    Friday, July 15, 2011 7:40 AM

Answers

  • Nitin,

    Clustered and Non clustered indexes are stored in B-tree structure .

    Clustered Index

    • Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order)
    • A table has only one clustered index because, the original table stored in leaf level of the clustered index (Data pages).
    • When you create a primary key by default clustered index will be created internally.(If the table has clustered index already then the non clustered index will be created internally)
    • If the table does not has clustered index it’s called “Heap”

    Non Clustered Index
    • Non clustered indexes are separate storage. (I.e. original table and an index stored separately)
    • Non clustered index does not enforce the logical order. The physical order of the rows is not the same as the index order.
    • A table has 999 non clustered indexes in sql-2008, 249 non clustered indexes prior to 2008. 
    • When you create a unique key by default non clustered index will be created internally.

    Muthukkumaran Kaliyamoorthy SQL DBA
    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    • Proposed as answer by Peja Tao Monday, July 18, 2011 7:24 AM
    • Marked as answer by Peja Tao Monday, July 18, 2011 7:24 AM
    Friday, July 15, 2011 8:53 AM

All replies

  • There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

    Please check for more http://devtoolshed.com/content/clustered-index-vs-non-clustered-index-sql-server


    http://uk.linkedin.com/in/ramjaddu
    Friday, July 15, 2011 8:01 AM
  • thanks ram

    can you give me any example to explain phycally reorder table in cluster index and logically order in noncluster index

    Friday, July 15, 2011 8:06 AM
  • a good analogy for clustered and non-clustered is:

    clustered index  - a dictionary, when you find a word in a dictionary, you find the meaning of the word with it. so if the word is the index key, and the meaning of the word is the data, the index and the data are stored in the same order.

    non-clustered - an index of a book, when you find the word in the index, the index points you to a page number in the book and you have to go to that page.

    Friday, July 15, 2011 8:22 AM
  • Clustered index you can imagine a phone book and names as clutered indexe key as all names are sorted in sequencially... ie data is physically ordered

    Where as non clusted indexe you can imagine with study books, you have index pages which direct you to particular page but data in the on pages are not ordered at all ... as data is not physically ordered but we have index to point to right page

    hope it make sence

    Please check this for requested detail http://www.sql-server-performance.com/2004/index-data-structures/

     


    http://uk.linkedin.com/in/ramjaddu
    Friday, July 15, 2011 8:23 AM
  • Nitin,

    Clustered and Non clustered indexes are stored in B-tree structure .

    Clustered Index

    • Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order)
    • A table has only one clustered index because, the original table stored in leaf level of the clustered index (Data pages).
    • When you create a primary key by default clustered index will be created internally.(If the table has clustered index already then the non clustered index will be created internally)
    • If the table does not has clustered index it’s called “Heap”

    Non Clustered Index
    • Non clustered indexes are separate storage. (I.e. original table and an index stored separately)
    • Non clustered index does not enforce the logical order. The physical order of the rows is not the same as the index order.
    • A table has 999 non clustered indexes in sql-2008, 249 non clustered indexes prior to 2008. 
    • When you create a unique key by default non clustered index will be created internally.

    Muthukkumaran Kaliyamoorthy SQL DBA
    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    • Proposed as answer by Peja Tao Monday, July 18, 2011 7:24 AM
    • Marked as answer by Peja Tao Monday, July 18, 2011 7:24 AM
    Friday, July 15, 2011 8:53 AM
  • Read the Gail's link which i have given. It'll give you clear picture.
    Muthukkumaran Kaliyamoorthy SQL DBA
    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Friday, July 15, 2011 8:54 AM
  • thank you!

    it's very helpful for me!

    thanks again.

    Monday, July 18, 2011 5:33 AM
  • You're welcome.
    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Monday, July 18, 2011 11:18 AM
  • very true....
    Wednesday, May 02, 2012 10:11 AM