Creating a Clustered Index

See Also

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 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.

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.

You can also use a clustered index to control what happens when a duplicate key or duplicate row is created as a result of adding or updating data to the indexed table (with the INSERT or UPDATE statement).

To create a clustered index

1. In your database diagram, select the table whose index you want to modify.
   
   
2. Right-click the table, and choose Properties from the shortcut menu.
   
   
3. Choose the Indexes/Keys tab.
   
   
4. Create a new index. For details, see Creating an Index.

   To modify an existing index, select the index from the Selected index list.

5. Select the Create as CLUSTERED check box.
   
   
6. Select one of the duplicate row options:

   Option	Treatment of duplicate rows
   Disallow duplicate rows	The default option. If duplicate rows exist, the index cannot be created when you attempt to save the table.
   Ignore duplicate rows	If duplicate rows exist, the index is created but duplicate rows are deleted when you save the table. When new rows are inserted, duplicates are not accepted and a SQL Server error appears when you attempt to save the table.
   Allow duplicate rows	Duplicate rows are accepted in new and existing data.

   
   

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