InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 13:02:37 GMT20http://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxI like this idea, purely as the very last ditch effort.Good job.Tue, 19 Jan 2010 18:48:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxHaving been there a couple of times over the years, I was glad to see this in writing. Igt is a fact of life, that sometimes you cannot use the backups. For a variety of reasons some places perform their backups knowing that the corruption is there but make the deliberate decesion to not fix it until later. I have done conversions where I have had to peek around the corruption using these methods. Thanks.Tue, 19 Jan 2010 15:47:28 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspx[quote][b]Charles Kincaid (1/19/2010)[/b][hr][quote]Clustered Indexes remove the data from the base table and store the data along with the pointer to the row in the table in the Clustered Index.[/quote] Well you learn something new every day. I was not aware of this. Thanks.[/quote]That isn't really a accurate description. The BOL description in [url=http://msdn.microsoft.com/en-us/library/ms177443.aspx]Clustered Index Structures[/url] is:[quote]In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list. [/quote]The Clustered Index holds the data for the table, there are no pointers back to a rown in the table, it is just a doubly linked list. That might be what you were going for, but the way you word that makes the concept change slightly.Tue, 19 Jan 2010 11:49:11 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxAbsolutely not. I am the first to stand up and say that having and testing backups is of the top priority. As stated earlier, this is the last ditch effort. I do not advocate creating extra indexing just in case however knowing ways to pull yourself from the fires of hell can sometimes be helpful.Tue, 19 Jan 2010 11:32:15 GMTRobert Hermsenhttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspx[quote]Clustered Indexes remove the data from the base table and store the data along with the pointer to the row in the table in the Clustered Index.[/quote] Well you learn something new every day. I was not aware of this. Thanks.:ermm: Please tell us that you are [b][i][u]not[/u][/i][/b] advocating adding covering indexes to hide database corruption and make folks feel better about less than adequate backups. Surely not.Tue, 19 Jan 2010 11:21:47 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxGreat ideaTue, 19 Jan 2010 08:07:05 GMTicatahttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspx[quote]The best resolution for database corruption is to restore to the last known good backup.[/quote]I am with you here.[quote]This is unfortunately not always possible.[/quote]But then you immediately lost me here. To quote Paul Randal "You don't have a backup until you've done a restore." If you can't restore your database from a backup, you haven't done your job as a DBA.The idea behind your article is great (I'd give you a 4 out of 5), but I think you missed the opportunity to drill it in that backup/restore should be a priority and that the process in your article is nothing more than a last ditch effort to salvage anything possible. If the table has LOB or BLOB columns, you won't be able to recover those because they can't be indexed with a NCI. Tue, 19 Jan 2010 06:40:44 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxCompletely agree with you Jason. This is that 'Last Ditch Effort' that nobody ever wants to have to resort to. Becomes the some data is better than no data scenario.Tue, 19 Jan 2010 06:39:34 GMTRobert Hermsenhttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxIts 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 [url=http://msdn.microsoft.com/en-us/library/ms177484.aspx]http://msdn.microsoft.com/en-us/library/ms177484.aspx[/url]-- Clustered Index Structures [url=http://msdn.microsoft.com/en-us/library/ms177443.aspx]http://msdn.microsoft.com/en-us/library/ms177443.aspx[/url]-- Included Columns [url=http://msdn.microsoft.com/en-us/library/ms190806.aspx]http://msdn.microsoft.com/en-us/library/ms190806.aspx[/url]Tue, 19 Jan 2010 00:03:08 GMTjasonwhowellhttp://www.sqlservercentral.com/Forums/Topic849519-2599-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Corruption/69096/">Recovering data from corrupt tables using non-clustered indexes</A>[/B]Mon, 18 Jan 2010 23:20:21 GMTRobert Hermsen