10

I have read tips about sql server database on http://www.sql-server-performance.com/2007/clustered-indexes

In conclusion section author mentioned: "Since you can only create one clustered index per table, take extra time to carefully consider how it will be used."

My question is:

*Why only one clustered index per table should be created in sql server? *

Hadi Sharifi
  • 1,497
  • 5
  • 18
  • 28
  • 5
    Because it's physically impossible to order a table according to two different orders. More information here: http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key –  Jan 30 '14 at 12:51
  • This applies to *most* DBMS – Kermit Jan 30 '14 at 15:26
  • 3
    This question appears to be off-topic because OP has not shown any research effort. – Kermit Jan 30 '14 at 15:26
  • Does this answer your question? [What do Clustered and Non clustered index actually mean?](https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean) – John K. N. Jan 14 '21 at 10:00

6 Answers6

22

Clustered Index:
Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically. Hence there can only be one clustered index per table.

Why care about clustered index?
If we put clustered index on the table then the data is retrieved much faster because sql server doesn't have to read the whole data -- Depends on query. But data retrieval is much faster.

NOTE: Although you can create more than one Non-Clustered index on a single table.

Noctis
  • 11,507
  • 3
  • 43
  • 82
Punter015
  • 1,718
  • 10
  • 13
  • why when I do a CREATE CLUSTERED INDEX [CustomerIdIndex] ON [dbo].[Customer] ([CustomerId]); GO CREATE NONCLUSTERED INDEX [CustomerGuidIndex] ON [dbo].[Customer] ([CustomerGuid]); I get an error that says more then one clustered index on table. I cant understand why it says that when I clearly have 1 of each type of index. – Mike Nov 29 '17 at 12:01
7

This is simply SQL Server's implementation decision. Theoretically, there are can be any number of clustering indexes. MongoDB and MyISAM have no clustering indexes and store data in a flat file. InnoDB for MySQL has one clustering index, the primary key, which may be hidden if a primary key is not declared. TokuDB for MySQL and TokuMX (both of which I work on) allow users to have multiple clustering indexes, with the implicit tradeoff being more disk space used for faster queries.

Zardosht Kasheff
  • 272
  • 1
  • 3
4

Because the clustered index is the way that the data in the table is ordered when it is written to disk. In other words, the clustered index is the table.

This is also why you cannot specify included columns on a clustered index - because by its nature all of the columns are already included.

paulH
  • 1,102
  • 16
  • 43
  • 6
    to add a possible useful comparison : just imagine the difference between an index at the end of a book, and a dictionary. The index at the end of the book is distinct from the book : it is a nonclustered index. In a dictionary, the index is part of the book, and the entire book is organized by that index, it is a clustered index. You can have only one order in a dictionary. – rudi bruchez Jan 30 '14 at 12:52
  • @rudibruchez the analogy is not perfect - someone will need to read the whole book in reverse order or ordered in some other way. – Bryn Nov 25 '22 at 12:59
3

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.

To create a different clustered index on the same table, change the Create as Clustered property setting on the existing clustered index before creating the second index.

124
  • 2,757
  • 26
  • 37
1

YOU CAN! (indirectly)

If you need multiple clustered index on a table, you could create indexed view on that table. But at the same time, you should be aware that clustered index always has its cost.

0

A clustered index determine the physical order of the data in the table , is like a home address that is why we should have only one home address , if not the postman get confused.

http://msdn.microsoft.com/en-us/library/aa933131.aspx

Amo A.
  • 70
  • 2