轉換非搜尋字伺服器組態選項transform noise words Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否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. 若全文檢索查詢使用的 CONTAINS 述詞中,布林運算或 NEAR 運算有包括非搜尋字,則這個選項很有幫助。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 DescriptionDescription
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" 識別碼為 1 之資料列的叫用Hit for row with ID 1
    "cat" NEAR "the""cat" NEAR "the" 識別碼為 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" 識別碼為 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)