SQL Interview: 93 Rebuilding a clustered index

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

The table dbo.Customers has a clustered index CX_dbo_Customers on the CustomerID column. It also has two non-clustered indexes. One is on the CustomerName column and has an included column of PhoneNumber. The other non-clustered index is on the CityName column.

You need to rebuild the clustered index. When you do this, will the non-clustered indexes also be rebuilt automatically?

Answer:

In the very early days of SQL Server this was true. However, ever since SQL Server 2000, the leaf level of a non-clustered index contains the clustering key when there is a clustered index. This means there is no need to rebuild the non-clustered indexes when the clustered index is rebuilt. So, they are not automatically rebuilt.

2025-12-03