transform noise words 服务器配置选项transform noise words Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

使用 转换干扰词 服务器配置选项可以取消干扰词(即 非索引字)导致全文查询的布尔操作返回零行时产生的错误消息。Use the transform noise words server configuration option to suppress an error message if noise words, that is stopwords, cause a Boolean operation on a full-text query to return zero rows. 此选项对于使用其布尔操作或 NEAR 操作包括干扰词的 CONTAINS 谓词的全文查询非常有用。This option is useful for full-text queries that use the CONTAINS predicate in which Boolean operations or NEAR operations include noise words. 下表中列出了该选项的可能值。The possible values are described in the following table.

ReplTest1Value 描述Description
00 不转换干扰词(或非索引字)。Noise words (or stopwords) are not transformed. 在某个全文查询包含干扰词时,该查询将返回零行,并且 SQL ServerSQL Server 将引发警告。When a full-text query contains noise words, the query returns zero rows, and SQL ServerSQL Server raises a warning. 这是默认行为。This is the default behavior.

注意:此警告是运行时警告。Note: The warning is a run-time warning. 因此,如果未执行全文查询子句,则不会引发警告。Therefore, if the full-text clause in the query is not executed, the warning is not raised. 对于本地查询,只引发一个警告,即使存在多个全文查询子句时也是如此。For a local query, only one warning is raised, even when there are multiple full-text query clauses. 对于远程查询,链接服务器可能无法中继错误;因此,可能无法引发警告。For a remote query, the linked server might not relay the error; therefore, the warning might not be raised.
11 转换干扰词(或非索引字)。Noise words (or stopwords) are transformed. 它们将被忽略,并且将对其余查询进行计算。They are ignored, and the rest of the query is evaluated.

如果用邻近词指定了干扰词,则 SQL ServerSQL Server 将删除它们。If noise words are specified in a proximity term, SQL ServerSQL Server removes them. 例如,干扰词 is 将从 CONTAINS(<column_name>, 'NEAR (hello,is,goodbye)')删除,并且将搜索查询转换为 CONTAINS(<column_name>, 'NEAR(hello,goodbye)')For example, the noise word is is removed from CONTAINS(<column_name>, 'NEAR (hello,is,goodbye)'), transforming the search query into CONTAINS(<column_name>, 'NEAR(hello,goodbye)'). 请注意, CONTAINS(<column_name>, 'NEAR(hello,is)') 将转换为简单 CONTAINS(<column_name>, hello) ,因为仅存在一个有效搜索词。Notice that CONTAINS(<column_name>, 'NEAR(hello,is)') would be transformed into simply CONTAINS(<column_name>, hello) because there is only one valid search term.

转换干扰词设置的影响Effects of the transform noise words Setting

本节将基于the转换干扰词 的替代设置,说明包含干扰词“”的查询的行为。This section illustrates the behavior of queries containing a noise word, "the", under the alternate settings of transform noise words. 假定示例全文查询字符串将对包含以下数据的表行运行: [1, "The black cat"]The sample full-text query strings are assumed to be run against a table row containing the following data: [1, "The black cat"].

备注

所有此类应用场景都可以生成干扰词警告。All such scenarios can generate a noise word warning.

  • 转换干扰词设置为 0:With transform noise words set to 0:

    查询字符串Query string 结果Result
    "cat" AND "the""cat" AND "the" 无结果(此行为对于 "the" AND "cat" 是相同的。)No results (The behavior is the same for "the" AND "cat".)
    "cat" NEAR "the""cat" NEAR "the" 无结果(此行为对于 "the" NEAR "cat" 是相同的。)No results (The behavior is the same for "the" NEAR "cat".)
    "the" AND NOT "black""the" AND NOT "black" 无结果No results
    "black" AND NOT "the""black" AND NOT "the" 无结果No results
  • 转换干扰词设置为 1:With transform noise words set to 1:

    查询字符串Query string 结果Result
    "cat" AND "the""cat" AND "the" 命中 ID 为 1 的行Hit for row with ID 1
    "cat" NEAR "the""cat" NEAR "the" 命中 ID 为 1 的行Hit for row with ID 1
    "the" AND NOT "black""the" AND NOT "black" 无结果No results
    "black" AND NOT "the""black" AND NOT "the" 命中 ID 为 1 的行Hit for row with ID 1

示例Example

以下示例将 转换干扰词 设置为 1The following example sets transform noise words to 1.

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
GO  
sp_configure 'transform noise words', 1;  
RECONFIGURE;  
GO  

另请参阅See Also

服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
CONTAINS (Transact-SQL)CONTAINS (Transact-SQL)