Friday 15 June 2012

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

4 comments: