SQL Server 2005 FTS unexpected results

Question

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

Answer 1

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" ')