Can we have primary key and clustered index on same table with different field?
Can we have primary key and clustered index on same table with different field?
This is one of my favourite interview question. I thought I will explain this in detail here.
Answer for the question ”Can we have primary key and clustered index on same table with different field” is YES we can create primary key on one field and clustered index on another field.
Let’s start with step by step by creating table, index and primary key.
First we will create a simple Customer table like below with three fields.
Create TABLE [Customer](
[id] [int] not null,
[name] [nchar](10) NULL,
[Phone] [nvarchar](50) NULL
) ON [PRIMARY]
Second, we will add clustered index on Phone field.
CREATE CLUSTERED INDEX [Ph_Ind] ON Customer
(
Phone ASC
) ON [PRIMARY]
and finally we will create primary key
ALTER TABLE Customer
ADD CONSTRAINT pk_ID PRIMARY KEY (id)
All this query will work fine without any issue. Now we will check the table and see what and all are created.
sp_help Customer
If you look at the last constraint type you will find that PRIMARY KEY (non-clustered) index has been created here.
What happened here is when you create primary key after creating clustered index, it will add non clustered index on that field.
I hope you are very clear about the explanation and concept.