This is one of those things that I found very interesting but figured I’d probably never really use. Of course then I needed it recently and it came in quite handy.

A while back Steve Stedman (b/t) started the database corruption challange. In the very first one Brent Ozar (b/t) used a rather neat trick to recover the data. It’s based on the fact that a non-clustered index contains some of the information from the table. Specifically any columns that are indexed, included or in the clustered index (if there is one). (Clustered index columns are included in the non-clustered indexes Part 1, Part 2)

USE AdventureWorks2014;
GO
SELECT rowguid, BusinessEntityID, StartDate, TerritoryID
FROM Sales.SalesTerritoryHistory
WITH (INDEX = AK_SalesTerritoryHistory_rowguid);

This particular query pulls the columns available from the AK_SalesTerritoryHistory_rowguid index of the Sales.SalesTerritoryHistory table. This includes the column indexed (rowguid) and the clustered index columns (BusinessEntityID, StartDate, TerritoryID). The data here is coming from the non-clustered index and not the clustered index.

So why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?

I have to start with the assumption that when you run CHECKDB the non-clustered indexes don’t have any errors. At that point by adding the INTO tablename clause to the query above you can pull the non clustered index data into a separate table. Now once the REPAIR_ALLOW_DATA_LOSS on the original corrupted table is complete you can compare the repaired table against the NCI data. Depending on your indexes you might even be able to completely re-construct the missing data.

Filed under: Index, Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication Tagged: index, microsoft sql server, problem resolution