I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere.
In one of my SQL Server 2005 table, I have following columns:
SerialNum ——- BigInt ——– Primary Key
District ——– nvarchar(50)
SchemeType —— nvarchar(50)
When I open the table in design mode using SQL Server Management Studio, I notice that Clustered Index property is enabled for District, whereas it is disabled for SchemeType.
If there can be only one Clustered Index, then why District has this property enabled when SerialNum is already defined as Primary Key?
If there can be more Clustered Indexes, then how to enable this property for SchemeType column?
Best Answer
Primary key and clustered index are two different things, though they are often used in conjunction. A couple of potential reasons your table ended up this way:
(a) it could have been created with a non-clustered primary key using
PRIMARY KEY NONCLUSTERED, then the clustered index was added.(b) the clustered index could have been added, then the primary key added (in which case it can't be clustered since there is already a clustered index).
Either of those could have been intentional design decisions, accidents, changes in afterthought, etc.
Ignore the property in Management Studio's design view. The clustered index is the table, but it is defined by the column(s) specified. The other columns are still technically part of the clustered index, they're just not key columns in the index. Calling that "enabled/disabled" is misleading.