Creating a Clustered Index

In Microsoft® SQL Server™ databases you can create a clustered index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the index key values. A table can contain only one clustered index. UPDATE and DELETE operations are often accelerated by clustered indexes because these operations require large amounts of data to be read.  Creating or modifying a clustered index can be time-consuming, because it is during these operations that the table's rows are reorganized on disk.

Consider using a clustered index for:

• Columns that contain a limited number of unique values, such as a state column that contains only 50 unique state codes.
  
  
• Queries that return a range of values, using operators such as BETWEEN, >, >=, <, and <=.
  
  
• Queries that return large result sets.

To create a clustered index

1. In your database diagram, select the table you want to index, right-click the table, and choose Indexes/Keys from the shortcut menu.

   -or-

   Open the Table Designer for the table you want to index, right-click in the Table Designer, and choose Indexes/Keys from the shortcut menu.

2. Create a new index. For details, see Creating an Index.
   To modify an existing index, select the index from the Selected index list.
   
   
3. Select the Create as CLUSTERED check box.

The index is created in the database when you save the table or diagram.

See Also

Indexes | Saving, Displaying, and Updating an Index Definition