.png)
CREATE FULLTEXT INDEX (Transact-SQL)
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.
CREATE FULLTEXT INDEX ON table_name
[ ( { column_name
[ TYPE COLUMN type_column_name ]
[ LANGUAGE language_term ]
} [ ,...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 }
}
- 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.
NoteAt 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 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) 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 Best Practices for 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 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.
- 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.
For more information about full-text indexes, see Configuring Full-Text Catalogs and Indexes for a Database.
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.
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 Full-Text Index Population.
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. |
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 AdventureWorks2008R2 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 AdventureWorks2008R2; 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 index on the ReviewerName, EmailAddress, and Comments columns of the Production.ProductReview table of the AdventureWorks2008R2 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 the default full-text catalog and an existing unique key index, PK_ProductReview_ProductReviewID. As recommended, this index key is on an integer column, ProductReviewID.
USE AdventureWorks2008R2;
GO
CREATE FULLTEXT INDEX ON Production.ProductReview
(
ReviewerName
Language 1033,
EmailAddress
Language 1033,
Comments
Language 1033
)
KEY INDEX PK_ProductReview_ProductReviewID ;
GO
C. Creating a full-text index without populating it
The following example creates a full-text catalog, documents_catalog, in the AdventureWorks2008R2 sample database. The example then creates a full-text index that uses this new catalog. The full-text index is 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.
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 AdventureWorks2008R2;
GO
CREATE FULLTEXT CATALOG documents_catalog;
GO
CREATE FULLTEXT INDEX ON Production.Document
(
Title
Language 1033,
DocumentSummary
Language 1033,
Document
TYPE COLUMN FileExtension
Language 1033
)
KEY INDEX PK_Document_DocumentID
ON documents_catalog
WITH STOPLIST = SYSTEM , 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
