Pulling data from a non-clustered index


March 14, 2016 by Kenneth Fisher

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;
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.

2 thoughts on “Pulling data from a non-clustered index

  1. […] Kenneth Fisher shows using a non-clustered index potentially to reconstruct corrupted data on a clus…: […]

  2. Great !!! Champ are you!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: