Rebuilding an Index on a Heap Table

I have a huge table that doesn't have a clustered index but has a couple of nonclustered indexes. Because this table is so large, we occasionally receive index corruption errors such as "The index entry from row ID was not found in index ID 2, of table 1152723159." We've tried using the DBCC REINDEX command to correct these errors, but does this command actually have any effect on heap tables? If not, what should I do to rebuild an index on a heap table?

The DBCC REINDEX command has no effect on a heap table. You can use one of three methods to rebuild a heap. With the first method, you drop the nonclustered indexes and create a clustered index to order the data the way you want it. Then, you drop the clustered index and recreate the non-clustered indexes. With the second method, you use SELECT..INTO to make a copy of the heap, then either use sp_rename and reset your permissions to make the copy the primary heap table or truncate the table and use INSERT..SELECT to reinsert the data. The third method requires that you use BCP –N OUT (or another export method such as Data Transformation Services—DTS) to copy the data out of SQL Server, then truncate and reload the data.

—Patrick Conlan
Platform Program Manager
Microsoft Project Team