DBCC REINDEX question

[katbear]

I just want to verify: Running DBCC REINDEX *only* works if the table has a CLUSTERED index, correct?

My testing shows that heap tables are not affected at all by reindexing.

Just want to confirm this!!

 

[ptheriault]

The DBCC REINDEX command has no effect on a heap table.
You can use one of three methods to rebuild a heap. ( a table without a clustered index.)
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.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Thanks Paul. I assume in the 2nd & 3rd methods, when you reinsert/load the data, you make sure you insert/load in the order you want?

 

[ptheriault]

Correct, you use an order by clause.

- Paul
- If at first you don't succeed, find out if the loser gets anything.