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.
