none
One Clustered Index

    Question

  • Why only one clustered Index in SQL Server? Can you please explain in more detail?

    Saturday, February 22, 2014 10:15 AM

Answers

  • The leaf pages of a clustered index holds the data of the table, so multiple clustered indexes means that the data is more in one place.

    And, yes, you can have as many clustered indexes as you like, although only technically only one will be called the clustered index, and the other will considered as non-clustered. More exactly, that would be a non-clustered index which includes all columns in the table, either as key columns or as included columns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 22, 2014 10:53 AM
  • Why only one clustered Index in SQL Server? Can you please explain in more detail?


    Clustered index sorts whole table in a particular order according to cluster key .You cannot have two order in which table can be sorted .If there were 2 PK they would try to sort them in 2 order which is not possible so only one PK is allowed.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, February 22, 2014 10:37 AM
  • Adding to Shanky's point, HOWEVER....you can create clustered index with combination of columns(Composite) which means the SORT happens according to the combination of columns(leading and preceding).

    Ref:http://technet.microsoft.com/en-us/library/ms190457.aspx


    Saturday, February 22, 2014 10:43 AM
  • The clustered has two main properties:

    1. The table data is part of the index (in the leaf level)
    2. It is used by all other indexes to access data that is not part of the (nonclustered) index

    The first point is just a description of an index that covers all columns of the table. If you don't have any column with a very large possible size (such as text, varchar(max), etc.) then a nonclustered index can also cover all columns. From that point of view, the clustered index is just one covering index.

    The second point has to do with the index implementation. Without a clustered index, any index will point to the data page that stores the data for the index key. With a clustered index, any other index will point to the clustered index key, which in turn, includes the data at the leaf level.

    This second point is a fixed mechanism.


    Gert-Jan

    Sunday, February 23, 2014 12:22 PM
  • Why only one clustered Index in SQL Server? Can you please explain in more detail?

    Hi KIRAN,

    A clustered index sorts and stores the data rows in the table based on the index key values. Therefore only one clustered index can be created on each table because the data rows themselves can only be sorted in one order.

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, February 28, 2014 7:16 AM

All replies

  • Why only one clustered Index in SQL Server? Can you please explain in more detail?


    Clustered index sorts whole table in a particular order according to cluster key .You cannot have two order in which table can be sorted .If there were 2 PK they would try to sort them in 2 order which is not possible so only one PK is allowed.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, February 22, 2014 10:37 AM
  • Adding to Shanky's point, HOWEVER....you can create clustered index with combination of columns(Composite) which means the SORT happens according to the combination of columns(leading and preceding).

    Ref:http://technet.microsoft.com/en-us/library/ms190457.aspx


    Saturday, February 22, 2014 10:43 AM
  • The leaf pages of a clustered index holds the data of the table, so multiple clustered indexes means that the data is more in one place.

    And, yes, you can have as many clustered indexes as you like, although only technically only one will be called the clustered index, and the other will considered as non-clustered. More exactly, that would be a non-clustered index which includes all columns in the table, either as key columns or as included columns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 22, 2014 10:53 AM
  • The clustered has two main properties:

    1. The table data is part of the index (in the leaf level)
    2. It is used by all other indexes to access data that is not part of the (nonclustered) index

    The first point is just a description of an index that covers all columns of the table. If you don't have any column with a very large possible size (such as text, varchar(max), etc.) then a nonclustered index can also cover all columns. From that point of view, the clustered index is just one covering index.

    The second point has to do with the index implementation. Without a clustered index, any index will point to the data page that stores the data for the index key. With a clustered index, any other index will point to the clustered index key, which in turn, includes the data at the leaf level.

    This second point is a fixed mechanism.


    Gert-Jan

    Sunday, February 23, 2014 12:22 PM
  • Why only one clustered Index in SQL Server? Can you please explain in more detail?

    Hi KIRAN,

    A clustered index sorts and stores the data rows in the table based on the index key values. Therefore only one clustered index can be created on each table because the data rows themselves can only be sorted in one order.

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, February 28, 2014 7:16 AM
  • Hi Kiran,

    Any database engine design in such a way that only one clustered Index is allowed by the engine.

    Here is out of context but trying to co-relate the answer for you clear understanding.

    For example you have a book which has more than 500 pages along with a page bookmark.

    Now You are reading this book and assume that on daily basis you are reading 10 page per day let say you have read 50 pages where you will put the bookmark at a time on page 51 or many other places.

    It is something like that when when you create clustered index data engine create index key and that keys stored on page physically along with the table data. Whenever that index is used database engine always read and sort the data according to created index key.

    Let suppose if database engines would have allowed more than one clustered index than how database engine will decide that which index should be used to store the data physically.   

    It is like you can not put two bookmarks on different pages to remember the last read page in the book.

     Thanks & Regards,

    Rahul Neekhra

    http://www.sqlserverpeoples.com/Faq.aspx

    • Proposed as answer by Neekhra Rahul Friday, February 28, 2014 9:17 AM
    Friday, February 28, 2014 9:17 AM