29

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

tesmp
  • 621
  • 4
  • 10
  • 12

5 Answers5

43

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

Matthew
  • 10,244
  • 5
  • 49
  • 104
19

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."

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
7

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

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

Vishal Patel
  • 71
  • 1
  • 1
1

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 2
    This is not my understanding. The clustered index, per Joe's link, is about actual storage order. It's nonsensical to think that you could equally arrange data in two distinct orders... Rather you would order by one, then the other... hence clustered indexes. EDIT: Though I think whoever downvoted you was a bit harsh. – Matthew Jan 06 '11 at 19:30
  • 1
    @Matthew : It's not "nonsensical" to think of the same data being sorted in different orders. That's exactly what happens if you create two nonclustered indexes over the same set of columns - or even more so if you include extra columns in the nonclustered index using the INCLUDE clause. I would also like to know why I was downvoted. – nvogel Jan 06 '11 at 19:37
  • No, two non-clustered indexes creates "maps" for quickly locating data elements on the physical storage... they do not change *where* the bits are allocated. Relate it to physical papers in a filing drawer... you may have a tree system to quickly locate any paper, but the papers are still only stored in a single, physical order. This is like why non-sequential GUID's slow `INSERT` performance... every random GUID forces a re-sorting of all the records. – Matthew Jan 06 '11 at 19:54
  • The sorting of a nonclustered index works just the same as a clustered one - it's a doubly linked list of index pages. They get resorted just the same. The fact that nonclustered indexes include the cluster key as well is the main difference but it's not a hugely important one because NC indexes can easily contain the same data ad the clustered index. – nvogel Jan 06 '11 at 20:07
  • 1
    Not according the Microsoft, per Joe Stefanelli's link. It's pretty unambiguous that "the clustered index determines the physical order of data in a table." Logically, physical data can only exist in a single order. You could keep multiple physical copies and decide which to query based on some other index if you wanted to... but that's outside the scope of this question. – Matthew Jan 06 '11 at 20:12
  • 2
    The pages of the table are physically stored on disk in the order of the clustered key if one exists. I'm not sure why there is any confusion or doubt about this. – JNK Jan 06 '11 at 20:25
0

In sql server only one. DB2 offers the possibility to have more, but at a cost: two dimensional clustering takes a lot of memory (proportional to the square of rows)

Mikkel
  • 41
  • 1