Creating Models › Creating a Physical Model › Indexes › Table Index Editor › Define SQL Server 2000 Index Storage Properties

Define SQL Server 2000 Index Storage Properties

Use the Options tab in the SQL Server Table Index Editor to specify physical storage properties at the index level for the selected index for a SQL Server 2000 physical model.

To define SQL Server index properties

1. Right-click a table index in the Model Explorer and click Properties.

   The SQL Server Table Index Editor opens.

2. Select the table from the Table drop-down that contains the index that you want to define.
3. Select the index in the Navigation Grid that you want to define.

   Note: Click New on the toolbar to create a new 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.

4. Work with the following options:

   Table

   Displays the table to which the selected table index belongs.

   Previous

   Positions the editor on the previous table index in the Navigation Grid.

   Next

   Positions the editor on the next table index in the Navigation Grid.

   Sort

   Sorts the table indexes by alphabetic, reverse alphabetic, or index order. You select the method you want using the drop-down menu that opens after you click the Sort button.

   New

   Creates a new table index that becomes the current object in the editor.

   Delete

   Deletes the selected table index.

   Help

   Accesses online help for the editor.

   

   Lets you enter text to filter a large list of table index objects so you can quickly locate the one that you want.

   Show FK Indexes

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

   Physical Name

   Displays the physical name of the table index.

   Type

   Displays the table index type.

   Is Unique

   Specifies to allow only unique values in the table index. Clear this check box to allow non-unique values.

   Note: This option is unavailable when a primary or foreign key index is selected.

   Physical Only

   Specifies whether to have the index appear in the physical model only. If you want the index to appear in the logical model as a key group, clear the check box.

   Note: This option is unavailable when a primary or foreign key index is selected.

   Generate

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

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

   Pad Index

   Lets you specify the space to leave open on each node in the intermediate levels of the index.

   Note: If you use the PAD_INDEX option, make sure you enter a percentage in the FILLFACTOR field. PAD_INDEX uses the percentage specified in the FILLFACTOR field.

   Ignore Duplicate Keys

   Specifies to include the WITH IGNORE_DUP_KEY clause in the CREATE INDEX clause when it generates the schema.

   Drop Existing

   Lets you specify that a named, preexisting clustered or nonclustered index should be dropped and rebuilt.

   Fill Factor

   Lets you enter the percentage to use to indicate how full SQL Server should make each index page during index creation.

   No Recompute

   Lets you specify that outdated index statistics are not automatically recomputed.

   Filegroup

   Lets you select a filegroup in which to store the table that contains the selected index. Click the button to open the SQL Server File Group Editor to create or modify filegroups.

6. Click Close.

   Index storage properties are defined and the SQL Server Table Index Editor closes.

More information:

Define Sybase Segments