Physical Modeling › SQL Server Physical Property Editors › SQL Server Model Objects › SQL Server Indexes › Define SQL Server Full-Text Indexes

Define SQL Server Full-Text Indexes

Use the SQL Server Table Fulltext Index Editor to define a table full-text index so that it properly stores all of the full-text words and their locations for a given table in a SQL Server 2005/2008 physical model.

To define a SQL Server full-text index

1. Click Indexes, Fulltext on the Model menu.

   The SQL Server Table Fulltext Index Editor opens.

2. Select the table from the Table drop-down that contains the full-text index that you want to define, select the full-text index in the Navigation Grid to define, and work with the following options:

   Note: Click New on the toolbar to create a new full-text index for the selected table if one does not exist. Only one full-text index is allowed for a table.

   • Name

     Displays the table full-text index name. You can change the table full-text index name in this field.

   • Index

     Specifies the unique key table index to back the table full-text index.

   • Fulltext Catalog

     Specifies the full-text catalog to which the table full-text index belongs.

3. Click the General tab and work with the following options:
   • Fulltext Index Columns

     Specifies all of the columns assigned to a table full-text index and defines column name, column type, position and sort order, language, and population option. Select the columns to place into the table full-text index.

   • Change Tracking

     Specifies the change tracking properties for the table full-text index. This determines whether SQL Server maintains a list of changes to the indexed data in the full-text catalog. Select one of the following options from the drop-down list:

     Manual

     Specifies that the change-tracking log is manually generated.

     Auto

     Specifies to automatically update the table full-text index as data is modified in the associated tables.

     Off

     Specifies to not keep a list of changes to the indexed data.

     Off No Population

     Specifies to not keep a list of changes to the indexed data and does not automatically start a full population after a column is added or dropped from a table full-text index.

   • Disabled

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

   • Population

     Specifies the population properties for the table full-text index. Select one of the following options from the drop-down list:

     Full

     Specifies that every row of the table be retrieved for full-text indexing even if the rows were already indexed.

     Incremental

     Specifies to only retrieve rows for full-text indexing that were modified since the last population.

     Update

     Specifies to process all insertions, updates, or deletions since the last time the table full-text index was updated.

     Stop

     Specifies to stop a population in progress.

4. (Optional) Click the Comment tab and enter any comments that you want to associate with the table full-text index.
5. (Optional) Click the UDP tab to work with user-defined properties for the table full-text index.
6. Click Close.

   The table full-text index is defined and the SQL Server Table Fulltext Index Editor closes.