Manage Full-Text Indexes
Applies To: SQL Server 2016
In Object Explorer, expand the server.
Expand Databases, and then expand the database that contains the full-text index.
Right-click the table on which the full-text index is defined, select Full-Text index, and on the Full-Text index context menu, click Properties. This opens the Full-text index Properties dialog box.
In the Select a page pane, you can select any of the following pages:
Page Description General Displays basic properties of the full-text index. These include several modifiable properties and a number of unchangeable properties such as database name, table name, and the name of full-text key column. The modifiable properties are:
Full-Text Index Stoplist
Full-Text Indexing Enabled
Search Property List
For more information, see Full-Text Index Properties (General Page).
Columns Displays the table columns that are available for full-text indexing. The selected column or columns are full-text indexed. You can select as many of the available columns as you want to include in the full-text index. For more information, see Full-Text Index Properties (Columns Page). Schedules Use this page to create or manage schedules for a SQL Server Agent job that starts an incremental table population for the full-text index populations. For more information, see Populate Full-Text Indexes.
Note: After you exit the Full-Text Index Properties dialog box, any newly created schedule is associated with a SQL Server Agent job (Start Incremental Table Population on database_name.table_name).
Click OK. to save any changes and exit the Full-text index Properties dialog box.
Several Transact-SQL functions such as OBJECTPROPERTYEX can be used to obtain the value of various full-text indexing properties. This information is useful for administering and troubleshooting full-text search.
The following table lists the full-text properties related to indexed tables and columns and their related Transact-SQL functions.
|FullTextTypeColumn||TYPE COLUMN in the table that holds the document type information of the column.||COLUMNPROPERTY|
|IsFulltextIndexed||Whether a column has been enabled for full-text indexing.||COLUMNPROPERTY|
|IsFulltextKey||Whether the index is the full-text key for a table.||INDEXPROPERTY|
|TableFulltextBackgroundUpdateIndexOn||Whether a table has full-text background update indexing.||OBJECTPROPERTYEX|
|TableFulltextCatalogId||Full-text catalog ID in which the full-text index data for the table resides.||OBJECTPROPERTYEX|
|TableFulltextChangeTrackingOn||Whether a table has full-text change-tracking enabled.||OBJECTPROPERTYEX|
|TableFulltextDocsProcessed||Number of rows processed since the start of full-text indexing.||OBJECTPROPERTYEX|
|TableFulltextFailCount||Number of rows Full-Text Search did not index.||OBJECTPROPERTYEX|
|TableFulltextItemCount||Number of rows that were successfully full-text indexed.||OBJECTPROPERTYEX|
|TableFulltextKeyColumn||The column ID of the full-text unique key column.||OBJECTPROPERTYEX|
|TableFullTextMergeStatus||Whether a table that has a full-text index is currently in merging.||OBJECTPROPERTYEX|
|TableFulltextPendingChanges||Number of pending change tracking entries to process.||OBJECTPROPERTYEX|
|TableFulltextPopulateStatus||Population status of a full-text table.||OBJECTPROPERTYEX|
|TableHasActiveFulltextIndex||Whether a table has an active full-text index.||OBJECTPROPERTYEX|
Typically, the result of CONTAINSTABLE or FREETEXTTABLE rowset-valued functions need to be joined with the base table. In such cases, you need to know the unique key column name. You can inquire whether a given unique index is used as the full-text key, and you can obtain the identifier of the full-text key column.
Use a SELECT statement to call the INDEXPROPERTY function. In the function call use the OBJECT_ID function to convert the name of the table (table_name) into the table ID, specify the name of a unique index for the table, and specify the IsFulltextKey index property, as follows:
SELECT INDEXPROPERTY( OBJECT_ID('table_name'), 'index_name', 'IsFulltextKey' );
This statement returns 1 if the index is used to enforce uniqueness of the full-text key column and 0 if it is not.
The following example inquires whether the
PK_Document_DocumentID index is used to enforce the uniqueness of the full-text key column, as follows:
USE AdventureWorks GO SELECT INDEXPROPERTY ( OBJECT_ID('Production.Document'), 'PK_Document_DocumentID', 'IsFulltextKey' )
This example returns 1 if the
PK_Document_DocumentID index is used to enforce uniqueness of the full-text key column. Otherwise, it returns 0 or NULL. NULL implies you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.
Each full-text enabled table has a column that is used to enforce unique rows for the table (the uniquekey column). The TableFulltextKeyColumn property, obtained from the OBJECTPROPERTYEX function, contains the column ID of the unique key column.
To obtain this identifier, you can use a SELECT statement to call the OBJECTPROPERTYEX function. Use the OBJECT_ID function to convert the name of the table (table_name) into the table ID and specify the TableFulltextKeyColumn property, as follows:
SELECT OBJECTPROPERTYEX(OBJECT_ID( 'table_name'), 'TableFulltextKeyColumn' ) AS 'Column Identifier';
The following example returns the identifier of the full-text key column or NULL. NULL implies that you are using an invalid index name, the index name does not correspond to the table, the table does not exist, or so forth.
USE AdventureWorks; GO SELECT OBJECTPROPERTYEX(OBJECT_ID('Production.Document'), 'TableFulltextKeyColumn'); GO
The following example shows how to use the identifier of the unique key column to obtain the name of the column.
USE AdventureWorks; GO DECLARE @key_column sysname SET @key_column = Col_Name(Object_Id('Production.Document'), ObjectProperty(Object_id('Production.Document'), 'TableFulltextKeyColumn') ) SELECT @key_column AS 'Unique Key Column'; GO
This example returns a result set column named
Unique Key Column, which displays a single row containing the name of the unique key column of the Document table, DocumentID. Note that if this query contained an invalid index name, the index name did not correspond to the table, the table did not exist, and so forth, it would return NULL.
In SQL Server, all user-created databases are full-text enabled by default. Additionally, an individual table is automatically enabled for full-text indexing as soon as a full-text index is created on it and a column is added to the index. A table is automatically disabled for full-text indexing when the last column is dropped from its full-text index.
On a table that has a full-text index, you can manually disable or re-enable a table for full-text indexing using SQL Server Management Studio.
Expand the server group, expand Databases, and expand the database that contains the table you want to enable for full-text indexing.
Expand Tables, and right-click the table that you want to disable or re-enable for full-text indexing.
Select Full-Text index, and then click Disable Full-Text index or Enable Full-Text index.
In Object Explorer, right-click the table that has the full-text index that you want to delete.
Select Delete Full-Text index.
When prompted, click OK to confirm that you want to delete the full-text index.