Introduction:

In this post, We will check the effect of creating, dropping or rebuilding clustered index or Heap on non clustered indexes on the same table.

Demo:

Let's Create a Table first
CREATE Table IndexTest
(ID int IDENTITY,
Val varchar(100)
)

Insert The records in IndexTest table

INSERT INTO IndexTest
Select RAND()
GO 15000

Create Non Cluster Index

CREATE INDEX IDX_NC_Indextest ON IndexTest(Val)-- Non Cluster Index on Val column

Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc

Delete some records from Indextest table

DELETE FROM IndexTest Where ID%3=0

Create Cluster Index on IndexTest table

CREATE Clustered INDEX IDX_Clustered_Indextest ON IndexTest(ID)--Cluster Index on Val column

Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc

Creating cluster index also forced all non cluster indexes to Rebuild. Check the fragmention and Number of pages

Take Away #1 Creating Cluster Index , Rebuild all the non cluster Indexes on the same table.

DELETE FROM IndexTest Where ID%5=0

ALTER INDEX IDX_Clustered_Indextest ON IndexTest Rebuild

Check Index Fragmentation
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND dbtables.[name]='IndexTest'
ORDER BY
indexstats.avg_fragmentation_in_percent desc

Rebuilding Cluster Index do not force non cluster index to rebuild as Non cluster Indexes are poining to key values not RID

Take Away #2 Rebuilding Cluster Index , Do not Rebuild the non cluster Indexes

Drop the cluster Index

DROP INDEX IDX_Clustered_Indextest ON IndexTest

Check Index Fragmentation

Dropping the cluster Index forced all the non cluster indexes to rebuild as they have to point to RID again

Take Away #3 Dropping Cluster Index , Rebuild all the non cluster Indexes on the same table

Delete few more records from the table

DELETE FROM IndexTest Where ID%7=0

ALTER TABLE IndexTest Rebuild

Check Index Fragmentation

Rebuilding the heap forced all the non cluster indexes to rebuild as RID changed

Take away #4 Rebuilding Heap , Rebuild all the non cluster Indexes on the same table.

So we can conclude that:

1. Creating Cluster Index , Rebuild all the non cluster Indexes on the same table
2. Rebuilding Cluster Index , Do not Rebuild the non cluster Indexes
3. Dropping Cluster Index , Rebuild all the non cluster Indexes on the same table
4. Rebuilding Heap , Rebuild all the non cluster Indexes on the same table