How Creating,Dropping or Rebuilding Cluster Index or Heap affect Non clustered Indexes

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

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

2 thoughts on “How Creating,Dropping or Rebuilding Cluster Index or Heap affect Non clustered Indexes

  1. Hi Sauras,
    very good column, thank you for it!
    I was solving one problem with disabling indexes, because on customers HW is not much space nor much memory, and TempDB is on C drive and so on… so loading big bunches of data raises errors about insufficient space …etc. Only way how to load data is disable all indexes and use WITH (TABLOCK) hint in insert to make things faster and remove logging into DB LOG. So, this is nice, until point that table is not accessible as long as CLustered INdex is disabled, only way is to delete it. I was asking myself, what about the rest NonCLustered INdexes on table, shall I enable them or what to do? Answer is simple, once I start (re)creation of CLIN MSSQL internally rebuilds rest of NCLINes and probably that way it enables them. So, after Insert operation, I just create CLIN and that rest of indexes on table goin’ to be rebuilded (and there fore enabled in this step).
    Only explicit rebuild of CLIN will not take effect to NCLINs as mentioned in point 2.
    Cheers, Ondrej

    PS: for dropping CLIN it enables (rebuilds) NCLINs and ad point 4; for explicit rebuild HEAP it also enables (rebuilds) NCLINs

Leave a Reply

Your email address will not be published.