How to: Sort Data in a Clustered Index
When you create or modify a clustered index in a 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 CHECKDB" topic 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 does not require the data to be copied or nonclustered indexes to be rebuilt.
Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.
To sort index values
In Server Explorer, right-click the table with the index values you want to sort and from the shortcut menu click Open Table Definition.
Choose Indexes/Keys on the shortcut menu.
Select the index from the Selected index list.
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 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.
Finally, click Close.