PrimaryKeys can be Nonclustered too?

  • Article

Yesterday, I came across a case where composite keys were being used and not justifiable. The reason shared was “Primary key by default creates a Clustered index on the table”. A primary key by default creates a Clustered index.  This happens when you use the designer to create a table.

Create a simple table and designate one of the columns as a Primary key. Script out the table and observe the script

 

 

However this behaviour can be controlled / overridden as desired.

Execute script below on your “test” database

 

CREATE TABLE [dbo].[Test1](

     [Col1PK] [int] NOT NULL,

     [Col2ClusteredInd] [int] NOT NULL

) ON [PRIMARY]

 

Create Clustered Index Ixd1 on Test1 (Col2ClusteredInd)

 

ALTER TABLE dbo.test1 ADD CONSTRAINT

     PK_test1 PRIMARY KEY nonCLUSTERED

     (

     Col1PK)

 

For those of us new to these terms, here is a brief introduction

A “contents” page in the front of a book is a very useful feature. It tells us how many chapters the book has and the length of each chapter. Similarly if we know where to find things, we are faster, more efficient and less stressed. Creating a catalogue or “contents” for the database is called an Index.

An index is made up of a set of index nodes that are organized in a B-Tree structure. This structure is hierarchical in nature, with root node at the top of the hierarchy and leaf node at the last level as show below

 

Whenever a query is issued, it traverses down the tree until desired data is reached. Example: To find a value of 56

  • Start at the root node of 1-200
  • Choose 1-100 at the first intermediate level
  • Choose 51-100 at the second intermediate level
  • Choose 51-75 at the leaf level
  • Go record by record to find 56

Historically there has been 2 types of indexes

  • Clustered Index
  • Nonclustered Index

Clustered indexes store data at the leaf level. Also, data in Clustered index is ordered either in ascending or descending fashion. Hence there can be only 1 clustered index on a table

Nonclustered index on the other hand stores only pointers to the data and leaf level does not contain data.

Covering Index and column Store index are the variations that are available. We will look at them in detail

As indexes can help, wrong indexing can also deterioate performance apart from maintenance overheads. Number of columns in the index and the order of colums are important considerations to design indexes.