After you have dealt with several incidents of corruption, you will find yourself hoping for an index ID greater than 1 when you encounter corruption. Corruption in nonclustered indexes is the easiest form of corruption to fix as long as the underlying heap or clustered index is not corrupted.
Corruption in Nonclustered Indexes
When the error message from DBCC CHECKDB tells us an index ID, we can tell what kind of index it is by the ID value. A heap is index ID = 0, a clustered index is index ID = 1, and a nonclustered index is index ID > 1. To fix this corruption scenario, we will need to know the names of the object and index involved.
You can clearly see that the corruption is in a nonclustered index when checking the database integrity with SQL Integrity Check:
If we run DBCC CHECKDB on the sample corrupt database (CorruptionDemo_AdventureWorksDW2012.zip (12.22 MB)), it will report an Object Id = 341576255 and an index ID = 2. We can query sys.indexes to determine both of these values:
Select Object_Name(object_id) As TableName,
name As IndexName
Where object_id = 341576255
and index_id = 2;
Fixing this form of corruption is simple. You need to recreate the index. Unfortunately, you cannot rebuild the index because the rebuild process uses the existing index to build the new one. The same is true for creating the index with the DROP EXISTING option. You have two options for this. You can drop the existing index and then recreate it new or you can disable the existing index and rebuild it. When you rebuild a disabled index, it uses the underlying data rather than the existing index to rebuild it.
Here is two ways to fix the corrupted index in the sample database (link above):
-- Drop and create
Drop Index dbo.FactResellerSales.IX_FactResellerSales_CurrencyKey;
Create Index IX_FactResellerSales_CurrencyKey
-- Disable and rebuild
Alter Index IX_FactResellerSales_CurrencyKey
On dbo.FactResellerSales Disable;
Alter Index dIX_FactResellerSales_CurrencyKey
On dbo.FactResellerSales Rebuild;
As promised, this fix was easy peasy, lemon squeezy.