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,
index_description: nonclustered, unique, primary key located on PRIMARY
Hope the article is informative and thanking you to prove your valuable time on it
Posted by: MR. JOYDEEP DAS