Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server 2005 Property Editors › Define a Table Index in SQL Server 2005

Define a Table Index in SQL Server 2005

Use the SQL Server Table Index Editor to define table indexes in a SQL Server 2005 physical model.

To define a table index in SQL Server 2005

1. Expand the Tables node in the Model Explorer.
2. Expand the table in the Model Explorer that contains the table index to define.
3. Right-click the table index that you want to define and click Properties.

   The SQL Server Table Index Editor opens.

4. Work with the following options:

   Note: Click New on the toolbar to create either a new unique (alternate key, or AK) or non-unique (inverted entry, or IE) index. Use the Enter filter text box to filter a very large list of indexes to quickly locate the one that you want to define.

   Show FK Indexes

   Specifies to display FK (foreign key) indexes in the Navigation Grid.

   Physical Name

   Specifies the physical name of the index. Change the physical name of the index in this field.

   AK ID

   Displays key designation of the index.

   Is Unique

   Specifies if the index is unique or non-unique. Select the check box if it is a unique index.

   Physical Only

   Specifies whether the index should be suppressed from a logical model and appear in a physical model only.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

   Generate As Constraint

   Generates the index as a constraint. Select the check box to enable this option. When this options is enabled, the Constraint Name field on the General tab is populated with the index name.

5. Click the General tab and work with the following options:

   Clustered

   Specifies whether the index is clustered or nonclustered.

   Constraint Name

   Defines the index name when you have specified to generate it as a constraint.

6. Click the Members tab and work with the following options:

   Index Members

   Lists the columns defined to the table and lets you specify the columns to place into the index. You can use the toolbar to specify sort order, move columns up or down in the list, invoke the Column Editor to manage column properties, or open the Reset Order dialog to reset column order.

   Also Include

   Specifies other non-key columns to include in the index. These columns are not part of the index but are stored along with the index columns in the index data area for fast retrieval. This optimizes retrieval speed because a read of this column is satisfied from the index data area, without accessing the row from the row data area. You can use the toolbar to specify sort order, move columns up or down in the list, or invoke the Column Editor to manage column properties.

7. Click the Options tab to further define the table index.
8. Click the Partitions tab to define partition columns. Partitioning both the table and its index can optimize performance for tasks such as queries and backups.
9. (Optional) Click the Comment tab and enter any comments that you want to associate with the table index.
10. (Optional) Click the Where Used tab to view where the table index is used in the model.
11. (Optional) Click the UDP tab to work with user-defined properties for the table index.
12. (Optional) Click the Notes tab to view history information and view or edit user notes.
13. Click Close.

    The table index is defined and the SQL Server Table Index Editor closes.

More information:

Define Options for a SQL Server 2005 Table Index

Define a Partition for a SQL Server Table Index

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor