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?



more ▼

asked Jun 15, 2012 at 03:46 PM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.


Hope this helps!

more ▼

answered Jun 15, 2012 at 03:57 PM

avatar image

14.4k 3 7 15

I just checked and that command is still present in SQL Server 2012.

Jun 15, 2012 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!


Jun 15, 2012 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.


Jun 15, 2012 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, 2012 at 11:02 PM ThomasRushton ♦♦
  • on the fact that it doesn't guarantee the the order in which the data will be returned. ;-)

Jun 16, 2012 at 01:37 AM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 15, 2012 at 03:46 PM

Seen: 1634 times

Last Updated: Jun 16, 2012 at 01:37 AM

Copyright 2018 Redgate Software. Privacy Policy