Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 18 '12 at 16:11

This question came from our site for professional and enthusiast programmers.

    
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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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