Database Management System (DBMS) / Importance of Indexing

Primary Index / Clustering Index

📌 What is a Primary Index?

A Primary Index is an index created on a primary key column of a table.

🔧 Features:

  • Automatically created when you define a primary key.

  • It is unique — no two rows have the same primary key.

  • Index entries are in sorted order of the primary key.

  • Often dense index (i.e., every search key has a pointer).

🧠 Example:

CREATE TABLE Students (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(50)
);

This creates a primary index on RollNo.

📌 What is a Clustering Index?

A Clustering Index determines the physical order of data rows in the table. That means the table is stored on disk in the order of this index.

🔧 Features:

  • Only one clustering index per table.

  • Not necessarily on a primary key.

  • Often used for columns that are frequently searched or range-queried.

  • Can be either unique or non-unique.

  • Data rows themselves are stored in sorted order of the clustering key.

🧠 Example:

CREATE CLUSTERED INDEX idx_name ON Students(Name);

Here, the table will store its rows sorted by Name physically.

📊 Comparison Table: Primary Index vs Clustering Index

Feature

Primary Index

Clustering Index

Based on

Primary key column

Any column (not necessarily primary key)

Order of rows

May or may not determine row order

Determines actual physical row order

Number per table

Only one (on primary key)

Only one allowed

Uniqueness

Always unique

Can be unique or non-unique

Automatic/Manual

Created automatically with primary key

Created manually by user

Index structure

Usually dense

Usually sparse

Use case

Uniquely identify rows

Improve performance for range queries

📌 Visual Summary:

Imagine a book:

  • Primary Index: Like a table of contents pointing to chapters (but chapters are in any order).

  • Clustering Index: Like pages sorted by topic (data physically follows index order).

🔍 When to Use Which?

  • Use Primary Index for fast key-based access (e.g., lookup by Roll Number, ID, etc.).

  • Use Clustering Index if:

    • You do lots of range queries (e.g., WHERE salary BETWEEN 10000 AND 50000)

    • You often sort or group by a specific column.