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

Question

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 1

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.