Indexes/Keys Property Page (ADP)

This property page contains a set of properties for the indexes, primary keys, and unique constraints attached to the tables in your database diagram or in Table Designer. Indexes and constraints are not graphically represented in database diagrams.

Table name

Shows the name of the table in Table Designer or the selected table in your database diagram. If more than one table is selected in your database diagram, only the name of the first table is visible.

Selected index

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

Type

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

New

Choose this button to create a new index, key, or unique constraint for the selected database table.

Delete

Choose this button to remove the selected index, key, or constraint from the table.

Note:  If you try to delete a primary key that participates in relationships, a message appears asking you if you want to delete all the relationships, too. You cannot delete a primary key without first deleting the relationships it participates in.

Index name

Shows the name of the selected index. You can rename the index by entering a new name in this box.

Column name/Order

(Microsoft SQL Server 2000 only.) Shows the columns contributing to the index, primary key, or unique constraint, along with whether each column's values are arranged in ascending or descending order within the item. You can add, change, or remove column names in this list. You can also change the ascending/descending setting for each column.

Index Filegroup

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

For more information on creating and using file groups, 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 control how SQL Server reacts when a row whose key value equals an existing key value is inserted during a bulk insert operation. If you choose Ignore duplicate key, SQL Server will issue a warning message, ignore the offending incoming row and try to insert the remaining rows of the bulk insert operation. If you do not choose Ignore duplicate key, SQL Server will issue an error message and roll back the entire bulk insert operation.

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.

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.

Don't automatically recompute statistics

Select this option to tell SQL Server to use previously created statistics. This choice, available only for SQL Server 7.0 and higher databases, might degrade query performance, but will accelerate the index-building operation.

For more information on this option, see your SQL Server documentation.

Validation Text

(SQL Server 2000 only.) Shows the text that is displayed to the users whenever a row that violates the index, key, or constraint is entered.

For SQL Server 6.5, 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     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     Default. Prevents the index from being saved if duplicate rows exist. If duplicate rows exist, an error message appears.

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