CREATE FULLTEXT INDEX (Transact-SQL)CREATE FULLTEXT INDEX (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server 内数据库中的表或索引视图创建全文索引。Creates a full-text index on a table or indexed view in a database in SQL ServerSQL 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. 全文索引最多可以包含 1024 个列。A full-text index can contain up to 1024 columns.

主题链接图标 TRANSACT-SQL 语法约定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 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 column_name
全文索引中包含的列的名称。Is the name of the column included in the full-text index. 只能为 char、varchar、nchar、nvarchar、text、ntext、image、xml 或 varbinary 类型的列编制索引,以供全文搜索使用。Only columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max) can be indexed for full-text search. 若要指定多个列,请按如下方式重复 column_name 子句:To specify multiple columns, repeat the column_name clause as follows:

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

TYPE COLUMN type_column_name TYPE COLUMN type_column_name
指定表列的名称 (type_column_name),用于存储 varbinary(max) 或 image 文档的文档类型。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. 此列(称为类型列)包含用户提供的文件扩展名(.doc、.pdf、.xls 等)。This column, known as the type column, contains a user-supplied file extension (.doc, .pdf, .xls, and so forth). 类型列必须是 charncharvarcharnvarchar类型。The type column must be of type char, nchar, varchar, or nvarchar.

仅当 column_name 指定 varbinary、varbinary(max) 或 image 列(数据作为二进制数据存储在该列中)时,才指定 TYPE COLUMN type_column_name;否则 SQL ServerSQL Server 将返回错误。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 ServerSQL Server returns an error.

备注

在建立索引时,全文引擎使用每个表行的类型列中的缩写来标识对 column_name 中的文档使用哪个全文搜索筛选器。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 LANGUAGE language_term
存储在 column_name 中的数据的语言。Is the language of the data stored in column_name.

language_term 是可选的,可以将其指定为与语言区域设置标识符 (LCID) 对应的字符串、整数或十六进制值。language_term is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. 如果未指定任何值,则使用 SQL ServerSQL Server 实例的默认语言。If no value is specified, the default language of the SQL ServerSQL Server instance is used.

如果指定了 language_term,将使用它代表的语言对存储在 char、nchar、varchar、nvarchar、text 和 ntext 列中的数据编制索引。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. 如果未针对列将 language_term 指定为全文谓词的一部分,则该语言就是查询时使用的默认语言。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.

如果指定为字符串,则 language_term 对应于 syslanguages 系统表中的 alias 列值。When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. 字符串必须用单引号引起来,如 'language_term'。The string must be enclosed in single quotation marks, as in 'language_term'. 如果指定为整数,则 language_term 就是标识该语言的实际 LCID。When specified as an integer, language_term is the actual LCID that identifies the language. 如果指定为十六进制值,则 language_term 将以 0x 开头,后跟 LCID 的十六进制值。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.

如果该值是双字节字符集 (DBCS) 格式,则 SQL ServerSQL Server 会将其转换为 Unicode 格式。If the value is in double-byte character set (DBCS) format, SQL ServerSQL Server will convert it to Unicode.

对于指定为 language_term 的语言,必须启用断字符和词干分析器等资源。Resources, such as word breakers and stemmers, must be enabled for the language specified as language_term. 如果这些资源不支持指定的语言, SQL ServerSQL Server 将返回错误。If such resources do not support the specified language, SQL ServerSQL Server returns an error.

使用 sp_configure 存储过程可访问有关 MicrosoftMicrosoft SQL ServerSQL Server 实例的默认全文语言的信息。Use the sp_configure stored procedure to access information about the default full-text language of the MicrosoftMicrosoft SQL ServerSQL Server instance. 有关详细信息,请参阅本主题后面的 sp_configure (Transact-SQL)不熟悉的读者。For more information, see sp_configure (Transact-SQL).

如果非 BLOB 和非 XML 列包含多种语言的文本数据,或者列中存储的文本的语言未知,则可能适合使用非特定 (0x0) 语言资源。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. 但是,您应该先了解使用非特定 (0x0) 语言资源的可能后果。However, first you should understand the possible consequences of using the neutral (0x0) language resource. 有关使用非特定 (0x0) 语言资源的可能解决方案和后果的信息,请参阅创建全文检索时选择语言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.

对于存储在 XML 或 BLOB 类型列中的文档,在创建索引时,将使用文档内的语言编码。For documents stored in XML- or BLOB-type columns, the language encoding within the document will be used at indexing time. 例如,在 XML 列中,XML 文档中的 xml:lang 属性将标识语言。For example, in XML columns, the xml:lang attribute in XML documents will identify the language. 在查询时,除非将 language_term 指定为全文查询的一部分,否则将使用以前在 language_term 中指定的值作为全文查询的默认语言。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_SEMANTICSSTATISTICAL_SEMANTICS
适用范围:SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017)

创建作为统计语义索引一部分的附加关键短语和文档相似性索引。Creates the additional key phrase and document similarity indexes that are part of statistical semantic indexing. 有关详细信息,请参阅语义搜索 (SQL Server)For more information, see Semantic Search (SQL Server).

KEY INDEX index_name KEY INDEX index_name
table_name 的唯一键索引的名称。Is the name of the unique key index on table_name. KEY INDEX 必须是唯一的单键列,不可为 Null。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 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. 如果默认目录不存在,SQL ServerSQL Server 将返回错误。If no default catalog exists, SQL ServerSQL Server returns an error.

FILEGROUP filegroup_name FILEGROUP filegroup_name
针对指定的文件组创建指定的全文索引。Creates the specified full-text index on the specified filegroup. 该文件组必须已存在。The filegroup must already exist. 如果未指定 FILEGROUP 子句,则全文索引位于与基表或视图相同的文件组中(对于非分区表),或者位于主文件组中(对于分区表)。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 ] }CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
指定是否由 SQL ServerSQL Server 将对全文索引所覆盖的表列所做的更改(更新、删除或插入)传播到全文索引。Specifies whether changes (updates, deletes or inserts) made to table columns that are covered by the full-text index will be propagated by SQL ServerSQL Server to the full-text index. 通过 WRITETEXT 和 UPDATETEXT 所做的数据更改不会反映到全文索引中,也不能使用更改跟踪方法拾取。Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking.

MANUALMANUAL
指定必须通过调用 ALTER FULLTEXT INDEX …START UPDATE POPULATION Transact-SQLTransact-SQL 语句(手动填充)。Specifies that the tracked changes must be propagated manually by calling the ALTER FULLTEXT INDEX ... START UPDATE POPULATION Transact-SQLTransact-SQL statement (manual population). 您可以使用 SQL ServerSQL Server 代理来定期调用此 Transact-SQLTransact-SQL 语句。You can use SQL ServerSQL Server Agent to call this Transact-SQLTransact-SQL statement periodically.

AUTO 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。AUTO is the default.

OFF [ , NO POPULATION]OFF [ , NO POPULATION]
指定 SQL ServerSQL Server 不保留对索引数据的更改的列表。Specifies that SQL ServerSQL Server does not keep a list of changes to the indexed data. 如果未指定 NO POPULATION,则 SQL ServerSQL Server 创建索引后将对其进行完全填充。When NO POPULATION is not specified, SQL ServerSQL Server populates the index fully after it is created.

仅当 CHANGE_TRACKING 为 OFF 时,才能使用 NO POPULATION 选项。The NO POPULATION option can be used only when CHANGE_TRACKING is OFF. 如果指定了 NO POPULATION,则 SQL ServerSQL Server 在创建索引后不会对其进行填充。When NO POPULATION is specified, SQL ServerSQL Server does not populate an index after it is created. 仅当用户使用 START FULL POPULATION 或 START INCREMENTAL POPULATION 子句执行 ALTER FULLTEXT INDEX 命令之后,才会填充索引。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 }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. 如果未指定 STOPLIST,则 SQL ServerSQL Server 会将系统全文非索引字表与索引关联起来。If STOPLIST is not specified, SQL ServerSQL Server associates the system full-text stoplist with the index.

OFFOFF
指定没有与全文索引关联的非索引字表。Specifies that no stoplist be associated with the full-text index.

SYSTEM SYSTEM
指定应对此全文索引使用默认的全文系统 STOPLIST。Specifies that the default full-text system STOPLIST should be used for this full-text index.

stoplist_name stoplist_name
指定要与全文索引关联的非索引字表的名称。Specifies the name of the stoplist to be associated with the full-text index.

SEARCH PROPERTY LIST [ = ] property_list_name SEARCH PROPERTY LIST [ = ] property_list_name
适用范围:SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017)

将搜索属性列表与索引相关联。Associates a search property list with the index.

OFFOFF
指定不会将任何属性列表与全文索引相关联。Specifies that no property list be associated with the full-text index.

property_list_name property_list_name
指定要与全文索引关联的搜索属性列表的名称。Specifies the name of the search property list to associate with the full-text index.

RemarksRemarks

有关详细信息,请参阅创建和管理全文检索For more information about full-text indexes, see Create and Manage Full-Text Indexes.

在 xml 列上,可以创建一个全文检索以便为 XML 元素的内容编制索引,但忽略 XML 标记。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. 支持包含多种语言的格式正确的 XML 或 HTML 文档和片段。Well-formed XML or HTML documents and fragments containing multiple languages are supported. 有关详细信息,请参阅 结合使用具有全文搜索和 XML 列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.

更改跟踪和 NO POPULATION 参数的交互Interactions of Change Tracking and NO POPULATION Parameter

是否填充全文索引取决于是否启用了更改跟踪以及在 ALTER FULLTEXT INDEX 语句中是否指定了 WITH NO POPULATION。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 POPULATIONWITH NO POPULATION 结果Result
未启用Not Enabled 未指定Not specified 对索引执行完全填充。A full population is performed on the index.
未启用Not Enabled SpecifiedSpecified 在发出 ALTER FULLTEXT INDEX...START POPULATION 语句之前,不会进行任何索引填充。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

用户必须具有全文目录的 REFERENCES 权限以及表或索引视图的 ALTER 权限,或者必须是 sysadmin 固定服务器角色的成员、db_owner 固定数据库角色的成员或 db_ddladmin 固定数据库角色的成员。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.

如果指定了 SET STOPLIST,则用户必须具有指定非索引字表的 REFERENCES 权限。If SET STOPLIST is specified, the user must have REFERENCES permission on the specified stoplist. 此 STOPLIST 的所有者可授予此权限。The owner of the STOPLIST can grant this permission.

备注

授予 public 对 SQL ServerSQL Server 随附的默认非索引字表的 REFERENCE 权限。The public is granted REFERENCE permission to the default stoplist that is shipped with SQL ServerSQL Server.

示例Examples

A.A. 创建唯一索引、全文目录和全文索引Creating a unique index, a full-text catalog, and a full-text index

以下示例对 JobCandidateID 示例数据库中 HumanResources.JobCandidate 表的 AdventureWorks2012AdventureWorks2012 列创建全文索引。The following example creates a unique index on the JobCandidateID column of the HumanResources.JobCandidate table of the AdventureWorks2012AdventureWorks2012 sample database. 然后,该示例创建一个默认全文目录 ftThe example then creates a default full-text catalog, ft. 最后,该示例使用 Resume 目录和系统非索引字表对 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.B. 为多个表列创建全文索引Creating a full-text index on several table columns

以下示例在 production_catalog 示例数据库中创建一个全文目录 AdventureWorksThe 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. 此全文索引位于 ReviewerNameEmailAddressCommentsProduction.ProductReview 列上。The full-text index is on the on the ReviewerName, EmailAddress, and Comments columns of the Production.ProductReview. 对于每个列,该示例指定英语的 LCID 1033,这是列中的数据语言。For each column, the example specifies the LCID of English, 1033, which is the language of the data in the columns. 该全文索引使用现有的唯一键索引 PK_ProductReview_ProductReviewIDThis full-text index uses an existing unique key index, PK_ProductReview_ProductReviewID. 根据建议,此索引键位于整数列 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.C. 使用搜索属性列表创建全文索引而不填充该索引Creating a full-text index with a search property list without populating it

以下示例为 Title 表的 DocumentSummaryDocumentProduction.Document 列创建全文索引。The following example creates a full-text index on the Title, DocumentSummary, and Document columns of the Production.Document table. 该示例指定英语的 LCID 1033,这是列中的数据语言。The example specifies the LCID of English, 1033, which is the language of the data in the columns. 此全文索引使用默认的全文目录和现有的唯一键索引 PK_Document_DocumentIDThis full-text index uses the default full-text catalog and an existing unique key index, PK_Document_DocumentID. 根据建议,此索引键位于整数列 DocumentID 中。As recommended, this index key is on an integer column, DocumentID.

该示例指定 SYSTEM 非索引字表。The example specifies the SYSTEM stoplist. 它还指定搜索属性列表 DocumentPropertyList;有关创建该属性列表的示例,请参阅 CREATE SEARCH PROPERTY LIST (Transact-SQL)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. 随后,在非峰值时间,该示例使用 ALTER FULLTEXT INDEX 语句对新索引开始进行完全填充,并启用自动更改跟踪。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) ALTER FULLTEXT INDEX (Transact-SQL)
DROP FULLTEXT INDEX (Transact-SQL) DROP FULLTEXT INDEX (Transact-SQL)
全文搜索 Full-Text Search
GRANT (Transact-SQL) GRANT (Transact-SQL)
sys.fulltext_indexes (Transact-SQL) sys.fulltext_indexes (Transact-SQL)
使用搜索属性列表搜索文档属性Search Document Properties with Search Property Lists