Clustered vs Non-Clustered Index: What is the simple way?

Indexes in relational databases help quickly retrieve data from a Relational Table. Querying on indexes helps you optimize the time taken for a query to retrieve results from the tables – Indexes can significantly improve the speed of query execution.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Technically, you can create two types of Indexes in a SQL database –

• Clustered Indexes
• Non-Clustered Indexes

You can create an Index over a Column as below –

What are Clustered Indexes?

A Clustered Index sorts the column into order and maintains a hashed index for every row. This approach is similar to a Dictionary where the values point to actual data. By default, a Clustered Index is created for a PRIMARY KEY column. Hence you can create only a single CLUSTERED INDEX per table.

What are Non-Clustered Indexes?

A Non-Clustered Index is created on non-key columns in the table. These are maintained in the form of a TREE with leaves pointing to actual data rows. Hence you need additional storage for maintaining these references and links. A table can contain more than one Non-Clustered Indexes. These are also comparatively slower when compared to a Clustered Index, because you’re actually traversing a tree instead of looking up in a Dictionary.

To Summarize –

1. A CLUSTERED INDEX sorts the column and indexes similar to a DICTIONARY
2. Here the values point to actual data
3. By default, a CLUSTERED INDEX is created for a PRIMARY KEY column
4. A table can contain only a single CLUSTERED INDEX
5. A NON-CLUSTERED INDEX is created on non-key columns in the table, without any Sorting
6. These are maintained in the form of a TREE with leaves pointing to actual data rows
7. NON-CLUSTERED INDEXES take more space to maintain
8. A table can contain more than one NON-CLUSTERED INDEXES