Indexes/Keys Property Page

Options

Selected index

Shows the name of the first index for the selected table in your diagram. If more than one table is selected in your diagram, only the name of the first index for the first table is visible. To show properties for a different index, expand the Selected index drop-down list.

Type

Shows the index/key object type for the selected table: index, primary key, or unique.

New

Choose this button to create a new index for the selected database table. Enter properties for the index. For more information, see Creating an Index.

Delete

Choose this button to remove the selected index from the table. For more information, see Deleting an Index.

Note You cannot delete a primary key without removing any relationships between tables dependent upon the primary key.

Column name

Shows the list of columns in the index, primary key, or unique constraint. You can add, change, or remove column names in this list.

Index name

Shows the name of the selected index. You can rename the index by entering a new name in this box. For more information, see Renaming an Index.

Index file group

Select the name of the filegroup in which you want to store the selected index. You must have at least one user-defined filegroup for this setting to be enabled. This setting is only available for Microsoft^® SQL Server™ 7.0 databases. If you create a database object and do not specify its filegroup, SQL Server will assign it to the default filegroup. Initially, the default filegroup is the Primary filegroup.

For more information on creating and using filegroups, see your SQL Server documentation.

Create UNIQUE

Select this option to create a unique constraint or index for the selected database table. Specify whether you are creating a constraint or index by selecting either the Constraint or Index button.

Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.

Fill factor

Shows the fill factor that specifies how full each index page can be. If a fill factor is not specified, the database’s default fill factor is used. For more information, see Specifying a Fill Factor for an Index.

Pad Index If you specified a Fill Factor of more than zero percent, and you selected the option to create a unique index, you can tell SQL Server to use the same percentage you specified in Fill Factor as the space to leave open on each interior node. By default, SQL Server sets a two-row index size.

Create as CLUSTERED

Select this option to create a clustered index for the selected database table. For more information, see Creating a Clustered Index.

For SQL Server 6.5 or earlier, the following options are exposed.

Sort Data Options

Identify how the data is ordered in the index as records are added to the index.

Sort data (the default) Organizes data in ascending order.
Data already sorted Accepts the order of existing data.
Reorganize sorted data Reorganizes the data in ascending order. Select this option, for example, when the table becomes fragmented or to rebuild nonclustered indexes.

Duplicate Row Options

Identify how the index should handle duplicate rows.

Disallow duplicate rows (the default) Prevents the index from being saved if duplicate rows exist. If duplicate rows exist, an error message will appear.
Ignore duplicate rows Deletes duplicate rows from the index as it is being created.
Allow duplicate rows Creates the index, even though duplicate rows exist.

Don't automatically recompute statistics

Select this option to tell SQL Server to use previously created statistics. This choice, available only for Microsoft^® SQL Server™ 7.0 databases, may not produce optimal results and is not recommended.

For more information, see your SQL Server documentation.