Removing Forwarded Records from Heap Tables

@gnanau

DBCC SHOWCONTIG WITH TABLERESULTS can be used to determine forwarded records in SQL Server 2000

IJeb Reitsma

code doesn't work!

Very good article, indeed! I have question though: Is there a way to determine the forwarded records in SQL Server 2000?

Hi pelsql,

Thanks so much for your question. Lori Brown's response is below.

"I chose to create a clustered index on a sequential value column because 1) it is a best practice to set clustered indexes on columns that are in some type of sequential order (either numeric or alphabetic) and 2) because a clustered index on a column that is already in sequential order would cause the least amount of change to the heap table. One of the challenges of having a procedure that makes a structural change to a table is realizing that clustered indexes can have a profound effect on the order of the data in the table. I have experienced times when adding even a very innocuous non-clustered index has changed the sort order of data returned in queries resulting in user complaints that an application was suddenly returning incorrect data to users. In an effort to not disturb data in the heap table in any way except to remove fragmentation, I decided that an autoincrementing numeric column would be the best fit for a clustered index to be introduced to a heap table. You can create a clustered index on just about any column and still achieve the same effect of removing forwarded records but you would change the sort order of the data."


Megan Keller

Associate Editor, SQL Server Magazine

mkeller@sqlmag.com

I just wonder why creating a non-unique clustered index on any column with few null values is enough by itself to obtain the same result. Why is it necessary to have some identity value, or any kind of sequential value key, to achieve the same result? Aside of that this is a great discovery for me. I got a great argument against the absence of a clustered index here. I usually discourage developer to use heap because the naturally become fragmented. However they are some case where heap are interesting, especially when there is mostly just inserts to it, and they are never updated and there is few access on it that would benefit from a clustered index.