How many clustered indexes there can be in one table?

Question

In SQL Server 2008, how many clustered indexes there can be in one table?

Solution 1

Only one; and 999 non-clustered indexes http://msdn.microsoft.com/en-us/library/ms143432.aspx

Solution 2

One. As noted here:

"A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index."

Solution 3

For SQL Server 2005: 1 Clustered Index + 249 Nonclustered Index = 250 Index

For SQL Server 2008: 1 Clustered Index + 999 Nonclustered Index = 1000 Index

Solution 4

1.

Although there are certain reasons for it, it may seem a little strange that only one clustered index is permitted. The fact that the clustered index sorts the data internally doesn't really explain the reason for only having one such index because nonclustered indexes are sorted in exactly the same way as clustered ones. Nonclustered indexes can include all the data of a table in the same way that a clustered one does. So in at least some cases it could be quite reasonable to create multiple "clustered" indexes or simply to do away with the distinction altogether. But SQL Server won't allow you to create more than one.

View more solutions