Sorting Data in a Clustered Index

See Also

When you create or modify a clustered index in Microsoft® SQL Server™ database, you set an option to control when data is sorted in the index. You can either sort the data when the index is created, or sort and reorganize the data when the table has become fragmented. For details about what causes table fragmentation and for recommended solutions, see the DBCC statement in SQL Server Books Online.

The option to sort the data when the index is created is always the faster of these two options because it doesn’t require the data to be copied or nonclustered indexes to be rebuilt.

To sort index values

  1. In your database diagram, select the table whose index values you want to sort.

  2. Right-click the table, and choose Properties from the shortcut menu.

  3. Choose the Indexes/Keys tab.

  4. Select the index from the Selected index list.

  5. Make sure the Create as CLUSTERED option is selected.

If you are using SQL Server 6.5, the sort options are exposed and you have to complete an additional step.

  1. Choose one of the sort options:
    Option Action
    Sort data The default option. Sorts the data when the index is created.
    Data already sorted Eliminates the sort performed when a clustered index is created and verifies that the data has been sorted by checking each index value to determine whether it is higher than the previous one. If any row fails this check, the index cannot be created when you attempt to save the table.

    When the data satisfies the check, this option will always be faster than the Reorganize sorted data option because the data is not copied and nonclustered indexes will not be rebuilt. This option is useful when a fill factor is specified to compact or expand the pages on which a table is stored.

    For more information about fill factors, see Specifying a Fill Factor for an Index.
    Reorganize sorted data Same as Data already sorted, except that this option will always be slower because the data is copied and nonclustered indexes will be rebuilt. Reorganizing the data is a good idea when a table becomes fragmented and you want to re-sort index values.

Note   Data already sorted creates the clustered index with the SORTED_DATA option. Reorganize sorted data creates the clustered index with the SORTED_DATA_REORG option. If you select either option and any row fails the check, the attempt to create the index when you save the table will fail. In the event of such a failure, you can either fix the data or choose the Sort data option.