Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

up vote 5 down vote accepted

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.

share|improve this answer
    
Primary Key should have a clustered index or an unclustered? –  RPK Sep 14 '11 at 4:15
2  
Should your car have 18" wheels or 20" wheels? Should you paint your dining room tan or beige? As with a lot of design decisions, it depends. There is no magic "the primary key should always/never be clustered" answer. I'm not trying to dodge the question - in a lot of cases the primary key is clustered, but there are reasons. You seem to be after a broad generality that simply doesn't exist. –  Aaron Bertrand Sep 14 '11 at 4:18
    
Indeed, we have a few tables where the clustered index is not on the primary key column because that's what we need for performance reasons. There's no hard and fast rule. –  Wil Sep 15 '11 at 18:16
add comment

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

share|improve this answer
    
Is the Clustered Index not enabled by default when creating primary key? –  RPK Sep 13 '11 at 15:48
    
It is by default, but you can create first a clustered index, and only then add a PK (that will add a non clustered index). –  Marian Sep 13 '11 at 19:55
add comment

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.

share|improve this answer
    
You don't need Enterprise to create indexed views. You do need Enterprise to allow the optimizer to consider them without explicitly using the NOEXPAND hint. –  Aaron Bertrand Sep 14 '11 at 4:21
add comment
  • 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.)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.