PRIMARY KEY and INDEX

This article is related to PRIMARY KEY and INDEX.

A common misconnects is that the PRIMARY KEY is always CUSTERED index.  

Before going to the depth of this article I want to ask some common questions.

My questions are mentioned bellow.

1.    Is PRIMARY KEY is always CLUSTERED INDEX?

2.    Can Primary KEY be CREATED without CLUSTERED INDEX (NONCLUSTERD)?

3.    Can Primary KEY Exist WITHOUT Any INDEX?

Now I am explaining it one by one.

Is PRIMARY KEY is always CLUSTERED INDEX?

By default when we create the PRIMARY KEY it takes UNIQUE CUSTERED INDEX.  Here I am providing a simple example to illustrate my points.

CREATE TABLE my_Emp

       (EmpID   INT NOT NULL PRIMARY KEY,

        EmpName VARCHAR(50) NOT NULL)

GO       

sp_helpindex my_Emp

GO

-- Result

index_name	index_description	index_keys
PK__my_Emp__AF2DBA79525B36FA	clustered, unique, primary key located on PRIMARY	EmpID

Here in this example I am Just create a table where "EmpID" is the primary key. Please look at the result It showing UNIQUE CLUSTERED INDEX.

Can Primary KEY be CREATED without CLUSTERED INDEX (NONCLUSTERD)?

Yes, the Primary key can be created without CLUSTERED INDEX.  Here I am providing a simple example to illustrate my points.

DROP TABLE my_Emp

GO

CREATE TABLE my_Emp

       (EmpID   INT NOT NULL PRIMARY KEY NONCLUSTERED,

        EmpName VARCHAR(50) NOT NULL)

GO       

sp_helpindex my_Emp

GO

-- Result

index_name	index_description	index_keys
PK__my_Emp__AF2DBA78562BC7DE	nonclustered, unique, primary key located on PRIMARY	EmpID

Here in this Example I am using NONCLUSTERED keywords with PRIMARY KEY. It gives me UNIQUE NONCLUSTERED PRIMARY KEY. So the PRIMARY KEY by default is UNIQUE CLUSTERED but we can create UNIQUE NONCLUSTERED primary key.

Can Primary KEY Exist WITHOUT Any INDEX?

NO. The PRIMARY KEY can not be exists without any Index. Here I am providing a simple example to illustrate my points.

DROP TABLE my_Emp

GO

CREATE TABLE my_Emp

       (EmpID   INT NOT NULL,

        EmpName VARCHAR(50) NOT NULL)

GO

CREATE CLUSTERED INDEX indx_my_Emp ON my_Emp (EmpName)

GO

ALTER TABLE my_Emp

ADD PRIMARY KEY(EmpID)

GO

       

sp_helpindex my_Emp

GO

-- Result

index_name	index_description	index_keys
indx_my_Emp	clustered located on PRIMARY	EmpName
PK__my_Emp__AF2DBA7859FC58C2	nonclustered, unique, primary key located on PRIMARY	EmpID

Here I am just craete a table without any index or parimay key (heap).  Then create a clustered index on columns  named "EmpName".  As only one primary key can exists in a table. Then I am createing a primary key on columns named :EmpID". Please look at the output. Here the Primary key takes UNIQUE NONCLUSTERED INDEX.

Conclutions

The PRIMARY KEY by defaults take the CLUSTERED INDEX. That's not means that we can craete the PRIMARY KEY with NONCLUSTERED INDEX.

It is necesasary to understand that the PRIMARY KEY columns is suitable for CLUSTERED INDEX or NOT. If not suitable for CLUSTERED INDEX, please don't create CLUSTERED INDEX on primary key.

Hope you like it.

Posted by: MR. JOYDEEP DAS