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

Define a View Index in SQL Server 2005

Use the SQL Server View Index Editor to define view indexes in a SQL Server 2005 physical model.

To define a view index in SQL Server 2005

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

   The SQL Server View 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.

   Name

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

   AK ID

   Displays key designation of the index.

   Disabled

   Specifies whether to disable the view index in the database. If the view index is disabled, it does not collect data. When it is not disabled, the view index is active. Select the disabled value from the drop-down list.

   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.

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 view 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 view index.
8. Click the Partitions tab to define partition columns.
9. (Optional) Click the Comment tab and enter any comments that you want to associate with the view index.
10. (Optional) Click the Where Used tab to view where the view index is used in the model.
11. (Optional) Click the UDP tab to work with user-defined properties for the view index.
12. (Optional) Click the Notes tab to view history information and view or edit user notes.
13. Click Close.

    The view index is defined and the SQL Server View Index Editor closes.

More information:

Define Options for a SQL Server 2005 View Index

Define a Partition for a SQL Server View Index

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor