question

jmcginley avatar image
jmcginley 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
sql-server-2005dbccclustered-index
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
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!
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Also, bear in mind that a clustered index does not guarantee the order in which data will be returned to your application.
1 Like 1 ·
JohnM avatar image JohnM commented ·
I just checked and that command is still present in SQL Server 2012.
0 Likes 0 ·
jmcginley avatar image jmcginley commented ·
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
0 Likes 0 ·
JohnM avatar image JohnM commented ·
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!
0 Likes 0 ·
JohnM avatar image JohnM commented ·
+1 on the fact that it doesn't guarantee the the order in which the data will be returned. ;-)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.