SQL Server 2005 FTS意外的结果 [英] SQL Server 2005 FTS unexpected results

问题描述

我有一个带有两列的索引视图，主键字符和全文索引varchar（300）的字段。我需要从MS Great Plains数据库中进行搜索，所以我创建了视图来填充来自主要表IV00101的串联值的字段。

  CREATE VIEW SearchablePartContent WITH SCHEMABINDING AS 
 SELECT ITEMNMBR，rtrim（ITEMNMBR）+''+ rtrim（ITMSHNAM）+''+ rtrim（ITMGEDSC）
作为SearchableContent 
 FROM dbo.IV00101 
 GO 
  - 在视图上创建索引以用作全文索引
 CREATE UNIQUE CLUSTERED INDEX IDX_ITEMNMBR ON SearchablePartContent（ITEMNMBR）
 CREATE FULLTEXT INDEX ON SearchablePartContent（SearchableContent ）KEY INDEX IDX_ITEMNMBR ON Cat1_PartContent 
 WHILE fulltextcatalogproperty（'Cat1_PartContent'，'populatestatus'）<> 0 
 BEGIN 
 WAITFOR DELAY '00：00：01'
 END 
  

问题是，当我用特定的关键字进行搜索时，它会产生意想不到的结果。例如一个简单的查询，比如：

$ p $ lt; code> SELECT * FROM SearchablePartContent WHERE CONTAINS（SearchableContent，'rotor'）

应该产生5个结果，而我得到1个。大约有72,000条记录被索引。但是，如果我做LIKE比较，我会得到预期的行数。我的数据并不复杂，这里有几个结果应该从我的查询中返回，但不是：

1. MN-H151536 John Chopper ，转子组件8820,9600,8820FRT MN-H152756 John Rotor，轴承9650STS，9750STS1


2. MN-H160613 John Rotor，轴承9650STS， 9750STS2

任何帮助将不胜感激。谢谢

解决方案

只是想一想：尝试用双引号括住搜索词，看看它是否有所作为。

  SELECT * FROM SearchablePartContent WHERE CONTAINS（SearchableContent，''rotor''）
  

I have an Indexed View with two columns, a primary key char and a field for full-text indexing varchar(300). I need to search from a MS Great Plains database, so I created the view to populate a field with concatenated values from my primary table IV00101.

CREATE VIEW SearchablePartContent WITH SCHEMABINDING AS 
SELECT ITEMNMBR, rtrim(ITEMNMBR)+' '+rtrim(ITMSHNAM)+' '+rtrim(ITMGEDSC)
 as SearchableContent
FROM dbo.IV00101
GO
-- create the index on the view to be used as full text key index
CREATE UNIQUE CLUSTERED INDEX IDX_ITEMNMBR ON SearchablePartContent(ITEMNMBR)
CREATE FULLTEXT INDEX ON SearchablePartContent(SearchableContent) KEY INDEX IDX_ITEMNMBR ON Cat1_PartContent
WHILE fulltextcatalogproperty('Cat1_PartContent','populatestatus') <> 0
BEGIN
 WAITFOR DELAY '00:00:01'
END

The problem is that when I do a search with particular keyword(s) it will yield unexpected results. For instance a simple query such as:

SELECT * FROM SearchablePartContent WHERE CONTAINS(SearchableContent, 'rotor')

should yield 5 results, instead I get 1. There's about 72,000 records indexed. However, if I do a LIKE comparison, I will get the expected rows. My data is not complex, here are a couple results that should be returned from my query, but are not:

1. MN-H151536 John Chopper, Rotor Assembly Monkey 8820,9600,8820FRT
2. MN-H152756 John Rotor, Bearing 9650STS,9750STS1
3. MN-H160613 John Rotor, Bearing 9650STS,9750STS2

Any help would be greatly appreciated. Thanks

解决方案

Just a thought: Try enclosing your search term with double quotes to see if it makes a difference.

SELECT * FROM SearchablePartContent WHERE CONTAINS(SearchableContent, ' "rotor" ')

这篇关于SQL Server 2005 FTS意外的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！