|
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
(comments are locked)
|
|
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! I just checked and that command is still present in SQL Server 2012.
Jun 15 '12 at 04:04 PM
JohnM
JohnM - We're on SQL 2005 and won't be getting to a later version soon - thanks for your answer and also for sending the link! John
Jun 15 '12 at 04:04 PM
jmcginley
Awesome! If I've answered your question effectively, please make sure to mark it as such so that others know that you've found an answer. Thanks!
Jun 15 '12 at 04:08 PM
JohnM
Also, bear in mind that a clustered index does not guarantee the order in which data will be returned to your application.
Jun 15 '12 at 11:02 PM
ThomasRushton ♦
+1 on the fact that it doesn't guarantee the the order in which the data will be returned. ;-)
Jun 16 '12 at 01:37 AM
JohnM
(comments are locked)
|
