SQL: Clustered and Non Clustered Index

Indexes in SQL are a great way to identify and organize records. Indexing a column can also optimize queries as it reduces overhead and improves efficiency on frequently referenced columns. There are different types of SQL indexes, though this blog focuses on the contrast between clustered and non-clustered indexes.

Clustered Index

A clustered index points to a block of physical data and does not require a separate table for the indexed column values as it directly identifies a record in a table. A primary key is a great example, as it defines the requirement for a clustered index, which requires unique key values and is sorted. There can only be one clustered index in a table.

Full table scans are avoided due to sorted records that can be easily identified and retrieved. Similar to how dictionary content is organized, the table of contents is most likely to be used as a reference to locate specific pages. If there is no clustered index in a table, then it becomes a heap due to unstructured records, which is suitable for data manipulation (insert, update and delete).

Non Clustered Index

A non-clustered index points to the address of a record that contains the indexed column key as a value in the physical table. The indexed column values are sorted and stored in a separate table. A table can have several non-clustered indexed columns, also known as secondary indexes.

Non-clustered indexes are a bit slower than clustered indexes and require disk space to accommodate key-value pairs. It is not ideal to have many secondary indexes, as it slows down data manipulation due to changes that must be reflected in the separate non-cluster index table. Though it is recommended to index columns as secondary indexes to avoid a full table scan.

Index it if

Index a column if it is important and frequently utilized in identifying a record, as it improves the performance of queries. In the end, consider the cost-benefit of using indexes in order to make a proper decision.