Is Primary Keys Are Always Clustered

No, this is Myth. We have only one clustered index on a table. When we are creating a primary key on the table and that table is not having any cluster index then by default it will create a cluster index by default, but if this table already has a cluster index then it will create a non-cluster index. 

Let’s see the below example

CREATE TABLE tblemp (  RowID int not null primary key ,empID int NOT NULL ,emp_name varchar(100) ,emp_add  varchar(100) );

In this example we have created a primary key, be default a cluster index is created on this table.

See the index type in this table

SELECT OBJECT_NAME(object_id) AS table_name ,name ,index_id ,type ,type_desc ,is_unique ,is_primary_key FROM sys.indexes WHERE object_id IN (OBJECT_ID('tblemp'));

See another example, here we are creating a table without primary key but creating a cluster index on a column.

CREATE TABLE tblemp1 (  RowID int NOT NULL ,empID int NOT NULL ,emp_name varchar(100) ,emp_add  varchar(100) ); CREATE CLUSTERED INDEX IX_tblemp1_empID ON tblemp1 (empID);

Table and index created successfully.

SELECT OBJECT_NAME(object_id) AS table_name ,name ,index_id ,type ,type_desc ,is_unique ,is_primary_key FROM sys.indexes WHERE object_id IN (OBJECT_ID('tblemp1'));

  

This index is not give the guarantee of uniqueness because this is not a unique key or primary key.

Now here we are adding the primary key constraint on this table. 

ALTER TABLE tblemp1 ADD CONSTRAINT PK_tblemp1_RowID PRIMARY KEY (RowID);

Constraint added successfully.

See primary key create a non-cluster index. Only one primary key nd one cluster index we can create on a table.