CREATE FULLTEXT INDEX (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Creates a full-text index on a table or indexed view in a database in SQL Server. Only one full-text index is allowed per table or indexed view, and each full-text index applies to a single table or indexed view. A full-text index can contain up to 1024 columns.

Transact-SQL syntax conventions

Syntax

CREATE FULLTEXT INDEX ON table_name
   [ ( { column_name
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ]
             [ STATISTICAL_SEMANTICS ]
        } [ , ...n ]
      ) ]
    KEY INDEX index_name
    [ ON <catalog_filegroup_option> ]
    [ WITH ( <with_option> [ , ...n ] ) ]
[;]

<catalog_filegroup_option>::=
 {
    fulltext_catalog_name
 | ( fulltext_catalog_name , FILEGROUP filegroup_name )
 | ( FILEGROUP filegroup_name , fulltext_catalog_name )
 | ( FILEGROUP filegroup_name )
 }

<with_option>::=
 {
   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
 | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
 | SEARCH PROPERTY LIST [ = ] property_list_name
 }

Arguments

table_name

The name of the table or indexed view that contains the column or columns included in the full-text index.

column_name

The name of the column included in the full-text index. Only columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max) can be indexed for full-text search. To specify multiple columns, repeat the column_name clause as follows:

CREATE FULLTEXT INDEX ON table_name (column_name1 [...], column_name2 [...]) ...

TYPE COLUMN type_column_name

Specifies the name of a table column, type_column_name, which is used to hold the document type for a 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(max) or image column, in which data is stored as binary data; otherwise, SQL Server returns an error.

LANGUAGE language_term

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 no value is specified, the default language of the SQL Server instance is used.

If language_term is specified, the language it represents is used to index data stored in char, nchar, varchar, nvarchar, text, and ntext columns. This language is the default language used at query time if language_term isn't specified as part of a full-text predicate against the column.

When specified as a string, language_term corresponds to the alias column value in the sys.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 converts it to Unicode.

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

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

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, it might be appropriate for you to use the neutral (0x0) language resource. However, first you should understand the possible consequences of using the neutral (0x0) language resource. For information about the possible solutions and consequences of using the neutral (0x0) language resource, see Choose a Language When Creating a Full-Text Index.

For documents stored in XML- or BLOB-type columns, the language encoding within the document is used at indexing time. For example, in XML columns, the xml:lang attribute in XML documents identifies 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.

STATISTICAL_SEMANTICS

Applies to: SQL Server (SQL Server 2012 (11.x) and later)

Creates the additional key phrase and document similarity indexes that are part of statistical semantic indexing. For more information, see Semantic Search (SQL Server).

KEY INDEX index_name

The name of the unique key index on table_name. The KEY INDEX must be a unique, single-key, non-nullable column. Select the smallest unique key index for the full-text unique key. For the best performance, we recommend an integer data type for the full-text key.

fulltext_catalog_name

The full-text catalog used for the full-text index. The catalog must already exist in the database. This clause is optional. If it isn't specified, a default catalog is used. If no default catalog exists, SQL Server returns an error.

FILEGROUP filegroup_name

Creates the specified full-text index on the specified filegroup. The filegroup must already exist. If the FILEGROUP clause isn't specified, the full-text index is placed in the same filegroup as base table or view for a nonpartitioned table, or in the primary filegroup for a partitioned table.

CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }

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 aren't reflected in the full-text index, and aren't picked up with change tracking.

• MANUAL

  Specifies that the tracked changes must 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 are 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 [ , NO POPULATION ]

Specifies that SQL Server doesn't keep a list of changes to the indexed data. When NO POPULATION isn't specified, SQL Server populates the index fully after it is created.

The NO POPULATION option can be used only when CHANGE_TRACKING is OFF. When NO POPULATION is specified, SQL Server doesn't populate an index after it is created. The index is only populated after the user executes the ALTER FULLTEXT INDEX command with the START FULL POPULATION or START INCREMENTAL POPULATION clause.

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

Associates a full-text stoplist with the index. The index isn't populated with any tokens that are part of the specified stoplist. If STOPLIST isn't specified, SQL Server associates the system full-text stoplist with the index.

• OFF

  Specifies that no stoplist is 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.

SEARCH PROPERTY LIST [ = ] property_list_name

Applies to: SQL Server (SQL Server 2012 (11.x) and later)

Associates a search property list with the index.

• OFF

  Specifies that no property list is associated with the full-text index.

• property_list_name

  Specifies the name of the search property list to associate with the full-text index.

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 Use Full-Text Search with XML Columns.

We recommend that the index key column is an integer data type. This provides optimizations at query execution time.

CREATE FULLTEXT INDEX can't be placed inside a user transaction. This statement must be run in its own implicit transaction.

For more information about full-text indexes, see Create and Manage Full-Text Indexes.

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.

For more information about populating full-text indexes, see Populate Full-Text Indexes.

Permissions

User must have REFERENCES permission on the full-text catalog and have ALTER permission on the table or indexed view, or be a member of the sysadmin fixed server role, or db_owner, or db_ddladmin fixed database roles.

If SET STOPLIST is specified, the user must have REFERENCES permission on the specified stoplist. The owner of the STOPLIST can grant this permission.

Examples

A. Create a unique index, a full-text catalog, and a full-text index

The following example creates a unique index on the JobCandidateID column of the HumanResources.JobCandidate table of the AdventureWorks2022 sample database. The example then creates a default full-text catalog, ft. Finally, the example creates a full-text index on the Resume column, using the ft catalog and the system stoplist.

CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)
   KEY INDEX ui_ukJobCand
   WITH STOPLIST = SYSTEM;
GO

B. Create a full-text index on several table columns

The following example creates a full-text catalog, production_catalog, in the AdventureWorks sample database. The example then creates a full-text index that uses this new catalog. The full-text index is on the ReviewerName, EmailAddress, and Comments columns of the Production.ProductReview. For each column, the example specifies the LCID of English, 1033, which is the language of the data in the columns. This full-text index uses an existing unique key index, PK_ProductReview_ProductReviewID. As recommended, this index key is on an integer column, ProductReviewID.

CREATE FULLTEXT CATALOG production_catalog;
GO

CREATE FULLTEXT INDEX ON Production.ProductReview (
    ReviewerName LANGUAGE 1033,
    EmailAddress LANGUAGE 1033,
    Comments LANGUAGE 1033
) KEY INDEX PK_ProductReview_ProductReviewID ON production_catalog;
GO

C. Create a full-text index with a search property list without populating it

The following example creates a full-text index on the Title, DocumentSummary, and Document columns of the Production.Document table. The example specifies the LCID of English, 1033, which is the language of the data in the columns. This full-text index uses the default full-text catalog and an existing unique key index, PK_Document_DocumentID. As recommended, this index key is on an integer column, DocumentID.

The example specifies the SYSTEM stoplist. It also specifies a search property list, DocumentPropertyList; for an example that creates this property list, see CREATE SEARCH PROPERTY LIST (Transact-SQL).

The example specifies that change tracking is off with no population. Later, during off-peak hours, the example uses an ALTER FULLTEXT INDEX statement to start a full population on the new index and enable automatic change tracking.

CREATE FULLTEXT INDEX ON Production.Document (
    Title LANGUAGE 1033,
    DocumentSummary LANGUAGE 1033,
    Document TYPE COLUMN FileExtension LANGUAGE 1033
) KEY INDEX PK_Document_DocumentID
WITH STOPLIST = SYSTEM,
    SEARCH PROPERTY LIST = DocumentPropertyList,
    CHANGE_TRACKING OFF,
    NO POPULATION;
GO

Later, at an off-peak time, the index is populated:

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;
GO

See also

• Create and Manage Full-Text Indexes
• ALTER FULLTEXT INDEX (Transact-SQL)
• DROP FULLTEXT INDEX (Transact-SQL)
• Full-Text Search
• GRANT (Transact-SQL)
• sys.fulltext_indexes (Transact-SQL)
• Search Document Properties with Search Property Lists