• Its important to note that the Non-clustered index DOES NOT contain the data of all columns. The data of all columns (except BLOBs/Text) in a row in SQL Server usually lives in the leaf level pages of the Clustered Index (if there is one). The NC index contains the key value in the covered columns of the NC index, and a uniqueified clustered index key value to locate the clustered index leaf pages, or the File ID+Row ID in the storage heap if there is no clustered index on the table. SQL 2005 and SQL 2008 offer "Included Columns" where the non-key data can be included in the NC index to help reduce navigational costs to get to the clustered index pages, however that is optional.

    Therefore, while navigation to get to row values using the NC Index is a good technique when you are out of options, it does not guarantee that you will get to the data when the database has corruption. If the clustered index leaf pages are corrupt, the data is corrupt. If only the intermediate or root page in the clustered index is corrupt, maybe using the NC indexes to get to the rows would work.

    See also

    -- NonClustered Index Structures http://msdn.microsoft.com/en-us/library/ms177484.aspx

    -- Clustered Index Structures http://msdn.microsoft.com/en-us/library/ms177443.aspx

    -- Included Columns http://msdn.microsoft.com/en-us/library/ms190806.aspx