SQL Server 2005 FTS unexpected results

Question

I have a two-column indexed view, a char primary key, and a varchar (300) full-text indexing field. I need to search from the MS Great Plains database, so I created a view to populate the field with the concatenated values ​​from my main 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 a specific keyword (s), it will give unexpected results. For example, a simple query, for example:

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

should give 5 results, instead I get 1. There are about 72,000 records registered. However, if I do a LIKE comparison, I will get the expected rows. My data is not complicated, here are some results that should be returned from my query, but not:

• MN-H151536 John Chopper, Monkey Rotor Assembly 8820.9600.8820FRT
• MN-H152756 John Rotor, Bearing 9650STS, 9750STS1
• MN-H160613 John Rotor, Bearing 9650STS, 9750STS2

Any help would be greatly appreciated. Thanks

Answer 1

Just think: try including your search query with double quotes to find out it doesn't matter.

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