What is a non-clustered index (non-clustered index)?
A non-clustered index is a separate structure stored outside the main table that contains:
- sorted keys (the ones the index is built on),
- pointers (references) to the actual rows in the table.
The principle
- The data in the table keeps its own order,
- A non-clustered index creates a "pointer card", like a separate table of contents that knows where every row sits.
Example
sql
CREATE NONCLUSTERED INDEX idx_users_email
ON users(email);Now the database builds a structure with sorted email values and references to the real rows.
When this query runs:
sql
SELECT * FROM users WHERE email = 'a@mail.com';SQL first looks up email in the index,
then follows the reference to the row in the table (this is called a lookup).
Details
- You can create many non-clustered indexes on one table.
- They speed up selection (
SELECT,WHERE,JOIN,ORDER BY). - But inserting/updating a row means updating every index that the changed field is part of.
Difference from a clustered index
- A clustered index sets the physical order of rows in the table.
- A non-clustered index is a "pointer" to the data, stored separately.
Short Answer
Interview readyPremium
A concise answer to help you respond confidently on this topic during an interview.