ALTER FULLTEXT INDEX (Transact-SQL)

Changes the properties of a full-text index.

Transact-SQL Syntax Conventions

Syntax

ALTER FULLTEXT INDEX ON table_name
   { ENABLE 
   | DISABLE
   | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }
   | ADD ( column_name 
     [ TYPE COLUMN type_column_name ] 
     [ LANGUAGE language_term ] [,...n] )
     [ WITH NO POPULATION ]
   | DROP (column_name [,...n] )
     [WITH NO POPULATION ] 
   | START { FULL | INCREMENTAL | UPDATE } POPULATION
   | {STOP | PAUSE | RESUME } POPULATION 
   | SET STOPLIST { OFF| SYSTEM | stoplist_name }
     [WITH NO POPULATION] 
   }
[;]

Arguments

• table_name
  Is the name of the table or indexed view that contains the column or columns included in the full-text index. Specifying database and table owner names is optional.

• ENABLE | DISABLE
  Tells SQL Server whether to gather full-text index data for table_name. ENABLE activates the full-text index; DISABLE turns off the full-text index.

  When the full-text index is disabled, the full-text index metadata remains in the system tables. If CHANGE_TRACKING is in the enabled state (automatic or manual update) when the full-text index is disabled, the state of the index freezes, any ongoing crawl stops, and new changes to the table data are not tracked or propagated to the index. Full-text index on table_name can be reactivated with ENABLE.

• SET CHANGE_TRACKING {MANUAL | AUTO | OFF}
  Specifies whether changes (updates, deletes, or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking.

  Note

  For information about the interaction of change tracking and WITH NO POPULATION, see "Remarks," later in this topic.

• MANUAL
  Specifies that the tracked changes will be propagated manually by calling the ALTER FULLTEXT INDEX … START UPDATE POPULATION Transact-SQL statement (manual population). You can use SQL Server Agent to call this Transact-SQL statement periodically.

• AUTO
  Specifies that the tracked changes will be propagated automatically as data is modified in the base table (automatic population). Although changes are propagated automatically, these changes might not be reflected immediately in the full-text index. AUTO is the default.

• OFF
  Specifies that SQL Server will not keep a list of changes to the indexed data.

• ADD | DROP column_name
  Specifies the columns to be added or deleted from a full-text index. The column or columns must be of type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max).

  Use the DROP clause only on columns that have been enabled previously for full-text indexing.

  Use TYPE COLUMN and LANGUAGE with the ADD clause to set these properties on the column_name. When a column is added, the full-text index on the table must be repopulated in order for full-text queries against this column to work.

  Note

  Whether the full-text index is populated after a column is added or dropped from a full-text index depends on whether change-tracking is enabled and whether WITH NO POPULATION is specified. For more information, see "Remarks," later in this topic.

• TYPE COLUMN type_column_name
  Specifies the name of a table column, type_column_name, that is used to hold the document type for a varbinary, varbinary(max), or image document. This column, known as the type column, contains a user-supplied file extension (.doc, .pdf, .xls, and so forth). The type column must be of type char, nchar, varchar, or nvarchar.

  Specify TYPE COLUMN type_column_name only if column_name specifies a varbinary, varbinary(max) or image column, in which data is stored as binary data; otherwise, SQL Server returns an error.

  Note

  At indexing time, the Full-Text Engine uses the abbreviation in the type column of each table row to identify which full-text search filter to use for the document in column_name. The filter loads the document as a binary stream, removes the formatting information, and sends the text from the document to the word-breaker component. For more information, see Full-Text Search Filters.

• LANGUAGE language_term
  Is the language of the data stored in column_name.

  language_term is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the default full-text language of the SQL Server instance is used.

  Use the sp_configure stored procedure to access information about the default full-text language of the SQL Server instance. For more information, see default full-text language Option.

  When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language. When specified as a hexadecimal value, language_term is 0x followed by the hex value of the LCID. The hex value must not exceed eight digits, including leading zeros.

  If the value is in double-byte character set (DBCS) format, SQL Server will convert it to Unicode.

  Resources, such as word breakers and stemmers, must be enabled for the language specified as language_term. If such resources do not support the specified language, SQL Server returns an error.

  For non-BLOB and non-XML columns containing text data in multiple languages, or for cases when the language of the text stored in the column is unknown, use the neutral (0x0) language resource. For documents stored in XML- or BLOB-type columns, the language encoding within the document will be used at indexing time. For example, in XML columns, the xml:lang attribute in XML documents will identify the language. At query time, the value previously specified in language_term becomes the default language used for full-text queries unless language_term is specified as part of a full-text query.

• [ ,...n]
  Indicates that multiple columns may be specified for the ADD or DROP clauses. When multiple columns are specified, separate these columns with commas.

• WITH NO POPULATION
  Specifies that the full-text index will not be populated after an ADD or DROP column operation or a SET STOPLIST operation. The index will only be populated if the user executes a START...POPULATION command.

  When NO POPULATION is specified, SQL Server does not populate an index. The index is populated only after the user gives an ALTER FULLTEXT INDEX...START POPULATION command. When NO POPULATION is not specified, SQL Server populates the index.

  If CHANGE_TRACKING is enabled and WITH NO POPULATION is specified, SQL Server returns an error. If CHANGE_TRACKING is enabled and WITH NO POPULATION is not specified, SQL Server performs a full population on the index.

  Note

  For more information about the interaction of change tracking and WITH NO POPULATION, see "Remarks," later in this topic.

• START {FULL|INCREMENTAL|UPDATE} POPULATION
  Tells SQL Server to begin population of the full-text index of table_name. If a full-text index population is already in progress, SQL Server returns a warning and does not start a new population.

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

  • INCREMENTAL
    Specifies that only the modified rows since the last population be retrieved for full-text indexing. INCREMENTAL can be applied only if the table has a column of the type timestamp. If a table in the full-text catalog does not contain a column of the type timestamp, the table undergoes a FULL population.

  • UPDATE
    Specifies the processing of all insertions, updates, or deletions since the last time the change-tracking index was updated. Change-tracking population must be enabled on a table, but the background update index or the auto change tracking should not be turned on.

• {STOP | PAUSE | RESUME } POPULATION
  Stops, or pauses any population in progress; or stops or resumes any paused population.

  STOP POPULATION does not stop auto change tracking or background update index. To stop change tracking, use SET CHANGE_TRACKING OFF.

  PAUSE POPULATION and RESUME POPULATION can only be used for full populations. They are not relevant to other population types because the other populations resume crawls from where the crawl stopped.

• SET STOPLIST { OFF| SYSTEM | stoplist_name }
  Changes the full-text stoplist to associate with the index.

  • OFF
    Specifies that no stoplist be associated with the full-text index.

  • SYSTEM
    Specifies that the default full-text system STOPLIST should be used for this full-text index.

  • stoplist_name
    Specifies the name of the stoplist to be associated with the full-text index.

  Note

  For information about the interaction of change tracking and WITH NO POPULATION, see "Remarks," later in this topic.

Remarks

On xml columns, you can create a full-text index that indexes the content of the XML elements, but ignores the XML markup. Attribute values are full-text indexed unless they are numeric values. Element tags are used as token boundaries. Well-formed XML or HTML documents and fragments containing multiple languages are supported. For more information, see Full-Text Index on an XML Column.

Interactions of Change Tracking and NO POPULATION Parameter

Whether the full-text index is populated depends on whether change-tracking is enabled and whether WITH NO POPULATION is specified in the ALTER FULLTEXT INDEX statement. The following table summarizes the result of their interaction.

Change Tracking	WITH NO POPULATION	Result
Not Enabled	Not specified	A full population is performed on the index.
Not Enabled	Specified	No population of the index occurs until an ALTER FULLTEXT INDEX...START POPULATION statement is issued.
Enabled	Specified	An error is raised, and the index is not altered.
Enabled	Not specified	A full population is performed on the index.

Permissions

The user must have ALTER permission on the table or indexed view, or be a member of the sysadmin fixed server role, or the db_ddladmin or db_owner fixed database roles.

If SET STOPLIST is specified, the user must have REFERENCES permission on the stoplist. The owner of the STOPLIST can grant this permission. In addition, granting REFERENCES permission requires ALTER FULLTEXT CATALOG permissions.

Note

The public is granted REFERENCE permission to the default stoplist that is shipped with SQL Server.

Examples

The following example modifies the full-text index in the JobCandidate table of the AdventureWorks database.

USE AdventureWorks;
GO
ALTER FULLTEXT INDEX ON HumanResources.JobCandidate ENABLE;
GO

See Also

Reference

sys.fulltext_indexes (Transact-SQL)

CREATE FULLTEXT INDEX (Transact-SQL)

DROP FULLTEXT INDEX (Transact-SQL)

Concepts

Full-Text Search (SQL Server)

Change History

Updated content
Updated the "Interactions of Change Tracking and NO POPULATION Parameter" section, correcting the values in the WITH NO POPULATION column for when change tracking is enabled.