How to modify an index (Query Analyzer)

How to modify an index (Query Analyzer)

To modify an index

1. On the Query menu, click Show Execution Plan.
   
   
2. Execute the Transact-SQL script in the query pane.
   
   
3. In the result pane, click the Execution Plan tab.
   
   
4. Right-click the icon of the physical operator that suggests that an index needs to be modified, and then click Manage Indexes. The database and table that the Graphical Execution Plan suggests need an index modified are automatically selected.
   
   
5. Optionally, in Database and Table, select the name of a different database and table.
   
   
6. In Existing indexes, click the name of the index to modify, and then click Edit.
   
   
7. In Column, select the column you want to appear in the index. Composite indexes can be created by selecting more than one column.
   
   
8. Optionally, select a column, and then click either Move Up or Move Down to change the order of the columns in the index.
   
   
9. Optionally, in Index options, select:
   • Unique values to create a unique index.
     
     
   • Clustered index to create a clustered index. If a clustered index already exists, this option is not available.
     
     
   • Ignore duplicate values to control what happens when an INSERT statement inserts multiple, nonunique key values into an index. For more information, see CREATE INDEX.
     
     
   • Do not recompute statistics (not recommended) to specify that index statistics are not automatically recomputed as the index is updated.
     
     
   • Filegroup to specify the filegroup on which to create the index. Click the name of the filegroup.
     
     
   • Pad index to leave space open on each interior node of the index. For more information, see CREATE INDEX.
     
     
   • Drop existing to delete any existing index of the same name before creating the new index.
     
     
   • Fill factor to specify how full SQL Server should make the leaf level of each index page during index creation. For more information, see CREATE INDEX.
10. Optionally, click Edit SQL to view and edit the Transact-SQL statement used to create the index.

See Also

Graphically Displaying the Execution Plan Using SQL Query Analyzer

Placing Indexes on Filegroups