CREATE FULLTEXT INDEX (Transact-SQL)

  • Article

Creates a full-text index on a table or indexed view in a database. 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.

Topic link icon 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
    Is the name of the table or indexed view that contains the column or columns included in the full-text index.

  • column_name
    Is 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, that 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.

    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 Configure and Manage Filters for Search.

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

    If language_term is specified, the language it represents will be 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 is not 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 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.

    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 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.

  • STATISTICAL_SEMANTICS
    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
    Is 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
    Is the full-text catalog used for the full-text index. The catalog must already exist in the database. This clause is optional. If it is not 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 is not 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 are not reflected in the full-text index, and are not 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 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 [ , NO POPULATION]
      Specifies that SQL Server does not keep a list of changes to the indexed data. When NO POPULATION is not 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 does not 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 is not populated with any tokens that are part of the specified stoplist. If STOPLIST is not specified, SQL Server associates the system full-text stoplist 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.

  • SEARCH PROPERTY LIST [ = ] property_list_name
    Associates a search property list with the index.

    • OFF
      Specifies that no property list be 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

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

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.

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.

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.

Note

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

Examples

A. Creating 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 AdventureWorks 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.

USE AdventureWorks;
GO
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. Creating 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 on the ReviewerName, EmailAddress, and Comments columns of the Production.ProductReview table of the AdventureWorks sample database. 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.

USE AdventureWorks;
GO
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. Creating 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.

USE AdventureWorks;
GO
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

Reference

ALTER FULLTEXT INDEX (Transact-SQL)

DROP FULLTEXT INDEX (Transact-SQL)

GRANT (Transact-SQL)

sys.fulltext_indexes (Transact-SQL)

Concepts

Create and Manage Full-Text Indexes

Full-Text Search (SQL Server)

Search Document Properties with Search Property Lists