SQL – Index Clustered vs Non-Clustered

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

In SQL server, indexes can be clustered indexes and non-clustered indexes.

When you create a clustered index on a table, all the rows in the
table are “physically” stored in the order of the clustered index key. So, there can be only one clustered index per table.

Non-clustered indexes have their own storage separate from the table data storage. Data are only “logically” stored in the order (with a row locator).

Appropriate indexes improve the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan.

However, too many indexes can slow down other data modification operations (such as INSERT, UPDATE, DELETE). This is because every time data changes in the table, all the indexes need to be updated. Another disadvantage of having too many or unnecessary indexes is that, indexes need disk space, the more indexes you have, more disk space is used.