1

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which command/s should be used:

  • Update statistics
  • Rebuild
  • Reorganize

EDIT

Scenario FooId is a clustered pk of type sequential uniqueidentifier, but for keeping it simple it this post we use #1 as id values.

#1 inserted

#2 inserted

#3 inserted


#1 selected

#1 deleted


#2 selected

#2 deleted


#2 inserted (that is reusing the clustered PK of #1)

#1 inserted (that is reusing the clustered PK of #1)


Isn't this going to break the sequential order? Will it not be stored like this now:

#3

#2

#1

//Daniel

2
  • Please explain more about having "messed up the order of the rows to not have sequential order of the FooId column anymore". Guess you are asking about logical fragmentation but not really clear TBH – Martin Smith Apr 15 '12 at 10:23
  • Look at the scenario I complemented with above. I hope it explains more. – Daniel Apr 15 '12 at 15:05
2

You may have misunderstood the way a clustered index works - by definition the data is logically ordered in the sequence of the clustering key, although it's possible for the clustered index to become physically fragmented as page-splits occur when new data is inserted.

If you want to reduce fragmentation either REBUILD or REORGANIZE can be appropriate. The rule of thumb is to do nothing when fragmentation is below 5%, REORGANIZE when fragmentation is between 5% and 30%, and REBUILD when fragmentation is above 30%.

Index fragmentation is recorded in the DMV sys.dm_db_index_physical_stats. See also the MSDN page on REBUILD/REORGANIZE

It's worth noting that unless you are running SQL Server Enterprise edition, REBUILD cannot be performed on-line - meaning that your table will be inaccessible for the duration of the command. REORGANIZE is always carried out on-line.

2
  • It's the clustered key I'm talking about. Look at the edited question above. I complemented with a scenario. – Daniel Apr 15 '12 at 15:06
  • @Daniel - read the links in my answer. The clustered index determines the order the data is stored in the table. – Ed Harper Apr 15 '12 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.