- A primary key is not an index. Primary keys uniquely identify columns in your table - nothing more and nothing less. You don't have to use them for indexing.
- A clustered index, reorders the physical rows of your table. So if you are consistently doing searches on a particular column AND it is unique in your table, you can use that column in a clustered index. Don't forget this. I always use the example of a telephone book. Clustered Indexes are like telephone books. Why would you want to have a telephone book ordered by a randomly generated Primary Key? The answer is that you wouldn't. Instead, you need a real world way to order your information.
- You SHOULD NOT let SQL Server decide the clustered index for you. It happens by default, but accounting for this is part of good database design.
- Setting up a clustered index that IS NOT your primary key is easy to do.
I love #4.... I love #4....repeat after me....
So with all of those things in mind, let me give you an example. Lets say that you have a table called Options. Your table is defined like this.
CREATE TABLE [dbo].[Options](
[OptionId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Options_OptionId] DEFAULT (newid()),
[OptionName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionDisplayName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionDescription] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Options_OptionStatus] DEFAULT ('ACTIVE'),
[LastStatusChangeDate] [datetime] NOT NULL CONSTRAINT [DF_Options_LastStatusChangeDate] DEFAULT (getdate()),
[CreatedBy] [uniqueidentifier] NOT NULL,
[DisplayOrder] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Options_DateAdded] DEFAULT (getdate())
) ON [PRIMARY]
Here is what this table looks like in the flesh without a key:
Looks good right? Now what? Well if you are like most people, your next step is to add the primary key! But this isn't correct. Adding the primary key will automatically create a clustered index. INSTEAD, we are going to create the clustered index FIRST! That's right! I said FIRST! You create the clustered index first and then you add the primary key after the fact.
Here is what you end up with.
You didn't see it, but the table is now physically ordered by the Display Order column - NOT the OptionId column. As you can see, we have a Clustered Index AND a Primary Key column which is unique, yet non-clustered. How did we do that? We did it by remembering to do the following:
- Create your Clustered Index FIRST - before you add a Primary Key!
- Add your unique Primary Key after you have added the Index.
This will allow you to have the clustered index set on the column that you actually should have it on instead of using the default set up that SQL Server offers you.
You don't know how to add a non primary key column as a clustered index in SQL Server.
Add the clustered index first on the column the table should be ordered on and then add the primary key.
* Note - Always remember to add your Clustered Index and then save and then add your primary key and then save AND THEN add your relationships. If you are using the SQL Database Diagram feature, don't try to add your clustered index and then use the diagram to add your primary key and relationships without saving the primary key BEFORE YOU ADD RELATIONSHIPS. If you add the clustered index and then go to the diagram to add the primary key and relationships WITHOUT SAVING the primary key before you add the relationships, when you save the primary key and relationships, the system will remove your custom clustered index and try to add the clustered index on the primary key only. This seems to be a quirk of using the database diagram feature.
Always remember the order -
- Add clustered index on your column
- Add primary key
- Add relationships