asked
Clustered indexes and DBCC DBREINDEX
The old DROP INDEX table.index CREATE CLUSTERED INDEX index ON table.columns (where the original index is clustered) did a great job of sorting the data in the table. DBCC DBREINDEX table reindexes all indexes. Does DBCC DBREINDEX also sort the data in the table if one of the indexes is clustered? Does DBCC DBREINDEX also sort the data in the table if the primary key is clustered? Thanks! John
Unless I mis-understand something here, yes, in a manner of speaking. The definition of the clustered index is to physically order the data based on your clustering key. The DBCC doesn't sort the data, the definition/nature of the clustered index does. The DBCC command just rebuilds the index. You can only have 1 clustered index on any table. If your primary key is also the clustering key, then yes the data will be sorted when you execute the DBCC command against it. As a side note, this command will be deprecated in future versions, so you might want to look at using 'ALTER INDEX'. It might be already deprecated in SQL Server 2012, but I would advise you to confirm that. You didn't mention what version of SQL Server you were using.
http://msdn.microsoft.com/en-us/library/ms181671.aspx Hope this helps!
Also, bear in mind that a clustered index does not guarantee the order in which data will be returned to your application.
9 People are following this question.
