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
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
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.