Primary Key And a Clustered Index

Question

Hi friends, I want to know What's the difference between a primary key and a clustered index?

Anil Kumar · Answer

Also, a table may not have any primary key. And may not have any index also. !!! Or can have both or any one. Check indexes on a table using this - EXEC sp_helpindex

Anil Kumar · Answer

Primary Key: It is used to enforce the uniqueness in a table. Uniqueness is handled by indexing. This indexing can be of any type i.e. Clustered as well Non-clustered. So we can say that without an index we can't have a Primary key. A primary key with non-clustered index may gives good performance over clustered primary key in some scenario. You may have a clustered indexing on other column (not with primary) which is mostly using for range search and your primary key with non-clustered index. All depends upon the requirements!!! Clustered Index: For clustered index, column doesn't need to be unique. Please re-read last line. Clustered index column may contain duplicate values, Null values!!!. There is no problem at all. This is not possible with Primary key. A primary key can't have null or duplicate values. But both can be only one in a table!!!

Senthilkumar · Answer

Hi, When you create the primary key on table then, it will create the clustered index. You can have only one clustered index per table. Since you can have only one primary key. At the same time you can create the clustered index by CREATE CLUSTERED INDEX statement. When you have clustered index on the table when every you do DML operation, it will do sorting automatically based on clustered index column. The clustered node will have the value.