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

Is the name of the column in table_name that holds the document type of column_name.

type_column_name must be of type char, nchar, varchar, or nvarchar. Specify type_column_name only if the column or columns in column_name are of type varbinary(max) or image; otherwise, SQL Server returns an error.

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

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) and default full-text language Option.

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

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 SQL Server maintains a list of all changes to the indexed data. Data changes through WRITETEXT and UPDATETEXT are not picked up with change tracking.

MANUAL

Specifies that the change-tracking log will be propagated either on a schedule using SQL Server Agent, or manually by the user.

AUTO

Specifies that SQL Server automatically updates the full-text index as the data is modified in the associated tables. AUTO is the default.

Note:
Although changes can be propagated automatically, these changes may not be reflected immediately in the full-text index. For more information about properties used for monitoring the full-text indexing process and for diagnosing any problems with full-text indexing, see Obtaining Full-Text Property Values Using Transact-SQL Functions.

OFF [ , NO POPULATION]

Specifies that SQL Server does not keep a list of changes to the indexed data.

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, or INCREMENTAL POPULATION clause. When NO POPULATION is not specified, SQL Server populates the index fully after it is created.

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 associate with the full-text index.

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

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.

The following example creates a full-text index on the HumanResources.JobCandidate table.

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

Reference

Other Resources

Help and Information