Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server 2008 Property Editors › Define a Table Full-Text Index in SQL Server 2008 and 2012

Define a Table Full-Text Index in SQL Server 2008 and 2012

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 2008 or 2012 physical model.

To define a table full-text index in SQL Server 2008 and 2012

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

   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.
3. Select the full-text index in the Navigation Grid that you want 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. Use the Enter filter text box to filter a very large list of full-text indexes to quickly locate the one that you want to define.

   Name

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

   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.

   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.

   Filegroup

   Specifies the file group used by the table full-text index.

   Generate

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

4. 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 with their name, position, and sort order, and lets you define column type, language, and population options. Select the check boxes of 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.

   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.

   Stop List

   Specifies the stoplist used by the table full-text index. Use the drop-down to select the stoplist or open the SQL Server Fulltext Stoplist Editor to work with full-text stoplists.

5. (Optional) Click the Comment tab and enter any comments that you want to associate with the table full-text index.
6. (Optional) Click the UDP tab to work with user-defined properties for the table full-text index.
7. (Optional) Click the Notes tab to view history information and view or edit user notes.
8. Click Close.

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