Physical Modeling › SQL Server Physical Property Editors › SQL Server Model Objects › SQL Server Indexes › SQL Server Table and View Indexes › Define SQL Server Table Indexes

Define SQL Server Table Indexes

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

To define a SQL Server table index

1. Click Indexes, Table on the Model menu.

   The SQL Server Table Index Editor opens.

2. Select the table from the Table drop-down for which you want to define an index and 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.

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

   • Generate

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

3. Click the General tab and work with the following options:
   • Clustered

     Specifies whether the index is clustered or nonclustered.

   • Physical Only

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

   • Constraint Name

     Defines the constraint associated with the index.

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

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

5. Click the Options tab to further define the table index.
6. Click the Partitions tab to define partition columns.
7. (Optional) Click the Comment tab and enter any comments that you want to associate with the table index.
8. (Optional) Click the UDP tab to work with user-defined properties for the table index.
9. Click Close.

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