transform noise words Option
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. 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.
Value | Description |
|---|---|
0 | Noise words (or stopwords) are not transformed. When a full-text query contains noise words, the query returns zero rows, and SQL Server raises a warning. This is the default behavior.  NoteThe 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. |
1 | Noise words (or stopwords) are transformed. They are ignored, and the rest of the query is evaluated. If noise words are specified in a proximity term, SQL Server removes them. 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)'). 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. |
This section illustrates the behavior of queries containing a noise word, "the", under the alternate settings of transform noise words. The sample full-text query strings are assumed to be run against a table row containing the following data: [1, "The black cat"].
|
Note
|
|---|
|
All such scenarios can generate a noise word warning. |
-
With transform noise words set to 0:
Query string
Result
"cat" AND "the"
No results (The behavior is the same for "the" AND "cat".)
"cat" NEAR "the"
No results (The behavior is the same for "the" AND "cat".)
"the" AND NOT "black"
No results
"black" AND NOT "the"
No results
-
With transform noise words set to 1:
Query string
Result
"cat" AND "the"
Hit for row with ID 1
"cat" NEAR "the"
Hit for row with ID 1
"the" AND NOT "black"
No results
"black" AND NOT "the"
Hit for row with ID 1
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
When I run the query:
select distinct wo_id, class_type_subtype_id,library_id,elogs_library_name from elogs_data where ( contains (*, 'draft') AND contains (*, 'perley') AND contains (*, 'cert') AND contains (*, 'of'))
I still get :
Informational: The full-text search condition contained noise word(s).
AND I GET NO ROWS RETURNED!
- 12/21/2011
- brqad
The exact informational message is as follows:
Informational: The full-text search condition contained noise word(s).
The next logical assumption is that even though someone would be so foolish as to add this into the information being returned from the server, surely there must be a way to disable this INFORMATIONAL message. Thus brings us to the Transform Noise Words Option, which for all intents and purposes seems to have no affect on this. It did not eliminate this ridiculous message. As such, in our situation, having found no solution, this is putting our migration to 2008 on hold indefinitely. It doesn't elminitate it in 2005 either, but in my opinion this, or some other setting SHOULD.
I'm furious that such a SEEMINGLY SMALL artifact would halt weeks of migration efforts. I'm currently awaiting a callback from Microsoft on this issue, but if Google is any indication, we are apparently out of luck.
UPDATE: The call back from MS yielded a short but sweet response; we're sorry, work around it.
Way to go Microsoft! We LOVE working around your bugs, it's what we live for!
- 4/29/2009
- SubnetZero
- 4/29/2009
- Thomas Lee
