Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize?

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

• 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

• Answer:

  You may have misunderstood the way a clustered index works - http://msdn.microsoft.com/en-us/library/ms177443%28v=sql.105%29.aspx 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 http://technet.microsoft.com/en-us/library/ms188917%28v=sql.105%29.aspx. See also http://technet.microsoft.com/en-us/library/ms189858%28v=sql.105%29.aspx 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.