Create a Clustered Index

Background information

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.

Create a clustered index

1. In the Database window, click Database Diagrams   under Objects, click the database diagram you want to open, and then click Design on the database window toolbar.
2. In your database diagram, select the table you want to index, right-click the table, and choose Properties from the shortcut menu.
3. Choose the Indexes/Keys tab, and then create a new index. For more information on each dialog box option, click Help.

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

4. Select the Create as CLUSTERED check box.

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