Datasource.What is the difference between clustered and non-clustered indexes?

🗃️ 1. Clustered Index

A clustered index determines the physical order of rows in a table.

• ✅ There can be only one clustered index per table.
• ✅ The data is stored directly in the index.
• ✅ The table is the index.

Example:

In a table with a clustered index on id, the rows are physically sorted by id.
Accessing a row via this index goes directly to the data — no lookup needed.

📂 2. Non-Clustered Index

A non-clustered index is a separate structure that points to the actual data rows.

• ✅ You can create multiple non-clustered indexes per table.
• ❌ Does not change the physical order of the data.
• 📎 Contains pointers (row IDs or clustered index keys) to access the actual data.

Example:

A non-clustered index on email will contain sorted email values with pointers to the actual row location (which is elsewhere).

---

🔍 Visual Analogy:

Type	Analogy
Clustered	A phonebook sorted by last name — the data is in the index
Non-Clustered	A book’s index — points you to a page where data lives

---

🧱 Example (SQL Server or MySQL InnoDB):

-- Clustered index (by default on PRIMARY KEY)
CREATE TABLE Users (
    id INT PRIMARY KEY,      -- clustered index
    email VARCHAR(100),
    name VARCHAR(100)
);

-- Non-clustered index on email
CREATE INDEX idx_email ON Users(email);

⚖️ Comparison Table

Feature	Clustered Index	Non-Clustered Index
Physical row order	✅ Yes (matches index order)	❌ No (separate structure)
Data stored in index	✅ Yes	❌ No (index points to data)
Number per table	Only 1	Many allowed
Speed (lookup)	✅ Fastest for range/equality on key	Slightly slower (requires extra lookup)
Size	Usually smaller	Slightly larger (includes pointers)

✅ When to Use:

Scenario	Use This Index Type
Primary key, sorting, fast retrieval	Clustered Index
Frequently searched non-key columns	Non-Clustered Index
Multiple searchable fields	Multiple Non-Clustered