PRIMARY KEY and NON CLUSTERD INDEX

 

 

When we are thinking about the PRIMARY KEY, side by side we are thinking about a clustered index. As the PRIMARY KEY by default create a clustered index. It is good idea that primary key should be CLUSTERED INDEXED.

In this article, I am telling something in opposite direction. We have to identify that the columns or columns we are choosing for primary key is good for CLUTERED INDEX or NOT.

If you choose a COMPOSITE primary key then selection of CLUSTERED INDEX is a bad idea as it fragmented your database quickly.  SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.

So please understand that the primary key and the clustered index are different concepts. One thing is common that the primary key takes a clustered index when it created by default.

We can create a primary key without a clustered index and put the clustered index in different columns if needed.

Here is the example of NONCLUSTERED Primary Key

CREATE TABLE my_tab2

            (ROLL    INTEGER NOT NULL PRIMARY KEY  NONCLUSTERED,

             SNAME   VARCHAR(50))

 

sp_helpindex my_tab2

 

--Output---

index_name: PK__my_tab2__44C28DB76831A297      

index_description: nonclustered, unique, primary key located on PRIMARY

index_keys: ROLL

Hope the article is informative and thanking you to prove your valuable time on it

Posted by: MR. JOYDEEP DAS