x

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
more ▼

asked Jun 15 '12 at 03:46 PM in Default

jmcginley gravatar image

jmcginley
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.

http://msdn.microsoft.com/en-us/library/ms181671.aspx

Hope this helps!

more ▼

answered Jun 15 '12 at 03:57 PM

JohnM gravatar image

JohnM
5.9k 1 3 7

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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x47
x14

asked: Jun 15 '12 at 03:46 PM

Seen: 1162 times

Last Updated: Jun 16 '12 at 01:37 AM