Why Clustered Index property is enabled on few columns and not on others?

Question

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?

Answer 1

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.

Answer 2

There can be only one clustered index, because the clustered index is the table data itself. It's a way of organizing the table's data based on a clustering key. It's similar to Oracle's index organized tables (IOT). Non clustered indexes are separate data structure. Clustered indexes are the data itself organized as a b-tree.

By default SQL Server makes the Primary Key as a clustered index, if there is not one already. It seems that in your case the Primary Key was created as a 'non clustered index' and the column District as a 'clustered index'.

Answer 3

You can have only one clustered index per table. A Primary key is not a clustered index. To answer your questions:

If there can be only one Clustered Index, then why District has this property enabled when SerialNum is already defined as Primary Key? This is because the clustered index is created on District. Only this will show as enabled and the rest will show up as disabled.

If there can be more Clustered Indexes, then how to enable this property for SchemeType column? You cannot have more than one clustered index on a table. You will have to drop the clustered index on District and recreate one for SchemeType. If you still want to keep the clustered index on District, then one trick you can use is to create an indexed view and then create a clustered index on on the view for SchemeType. You may need Enterprise edition for this.

Answer 4

• In MySQL, which I see from other posts you use, the primary key and clustered index are always the same.
• In SQL Server, they can be different.
• In SQL Server, there can only be one clustered index. The clustered index will ensure the table is physically ordered according to that index.
• Of course you can have many non-clustered indexes in addition to your one clustered index, and typically you will have one clustered and one or a few non-clustered indexes on some of your tables.
• Normally in SQL Server primary keys have the clustered index (and I think that's the default), but it is possible to configure another column (or columns) with the clustered index instead.
• It is even possible to have two or more columns in that clustered index. You don't say whether or not the SerialNum column also is clustered, but if you see that both are clustered, then you have a single index that spans two columns and there will be a clustering order integer property which will the order in which those columns determine physical table layout (for example, "ordered by District first, and within district, ordered by SerialNum"). Note that adding more columns to this clustered index will tend to reduce performance rather than improve it, unless the physical ordering exactly matches the main query cases against that table enough to overcome the reduced INSERT performance penalty that comes with ensuring that physical ordering.
• In your system, if the District is clustered (but SerialNum is not), that might be a good design if the queries against that table more often join against "District" or filter against "District" than "SerialNum". (I've seen this sort of thing in scenarios where something like SerialNum is a an auto-incremented surrogate key that isn't used much elsewhere in your data model and if end-users/applications/reports have joins/where/groupby/orderby clauses with District but rarely or never against SerialNum.)