CONTAINSTABLE查询无法识别小单词

Question

我使用CONTAINSTABLE搜索两个表列。一旦搜索包含像“the”这样的小单词“a”，搜索就不会返回任何结果，即使它们实际上出现在列中。

快速示例。要搜索的列包含文本。‘系统需要升级’

下面的SQL返回0行

SELECT     * FROM Incident WHERE (TicketNumber IN (
SELECT TicketNumber FROM         [Action] FT_TBL INNER JOIN
CONTAINSTABLE(Action, Text, '"system" AND "needs" AND "to" AND "upgraded" AND NOT "Search Summary"') KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
UNION
SELECT TicketNumber FROM [Incident] FT_TBL INNER JOIN
CONTAINSTABLE(Incident, Subject, '"system" AND "needs" AND "to" AND "upgraded"') AS KEY_TBL ON FT_TBL.TicketNumber = KEY_TBL.[KEY]))

一旦省略了“to”，它就能正常工作：

SELECT     * FROM Incident WHERE (TicketNumber IN (
SELECT TicketNumber FROM         [Action] FT_TBL INNER JOIN
CONTAINSTABLE(Action, Text, '"system" AND "needs" AND "upgraded" AND NOT "Search Summary"') KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
UNION
SELECT TicketNumber FROM [Incident] FT_TBL INNER JOIN
CONTAINSTABLE(Incident, Subject, '"system" AND "needs" AND "upgraded"') AS KEY_TBL ON FT_TBL.TicketNumber = KEY_TBL.[KEY]))

如何将CONTAINSTABLE与这些较小的单词一起使用，或者应该将它们完全省略？如果这些较小的单词在搜索中实际上是有意义的，那么如何将它们包含在搜索中？

Answer 1

这些不是“小单词”，它们是停用列表中的常见单词。它们会被忽略。

请参见BOL上的Stopwords and Stoplists。

Answer 2

有关从空开始创建和使用自定义全文索引字列表的信息，请参阅the end of my answer。我检查了一下，你的查询与"to"，"be"，"the"，"for“等的关系是空的。

更新：

SQL Server2005应具有MSSQL\FTData\noiseENG.txt。

我再次推荐查看question "Noise Words in Sql Server 2005 Full Text Search"的答案