Full-Text-Search Results Not as Expected

I'm using Microsoft SQL Server 2008. I'm not an expert with it but I created a full-text index and have been writing some queries.

It's working without errors and is returning some results, but rows I know should match are no开发者_如何转开发t always included.

Is there anyway to verify or inspect the index? I went in several times and "repopulated the index" so I'm pretty sure it's right. But what do you do when you don't seem to get the right results.

My query is fairly complex but here it is if anyone is thinking that's the problem:

DECLARE @StartRow int;
DECLARE @MaxRows int;

SET @StartRow = 1;
SET @MaxRows = 10;

WITH ArtTemp AS
(SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY ArtViews DESC) AS RowID,
Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle,
Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID,
[User].UsrDisplayName AS UserName
FROM Article
INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID
INNER JOIN Category ON Subcategory.SubCatID = Category.CatID
INNER JOIN [User] ON Article.ArtUserID = [User].UsrID
WHERE CONTAINS(Article.*,'FORMSOF(INFLECTIONAL,"HTML")'))

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,
ArtUpdated,ArtUserID,UserName
FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

In the query above, rows are returned. However, at least one row I know to contain the word "HTML" is not included.

Any troubleshooting tips?

I'm not a SQL expert, but 'SELECT TOP (@StartRow + @MaxRows)' to me translates as select the top 11 rows (start = 1 max = 10) that match the criteria, regardless of their RowID, not select from rows 1-10. Later you select your results 'WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)', which means only show the rows with a RowID between 2-11. That may be why you aren't receiving all of the results you are expecting. If that is not the case then I would make sure the rows you are expecting meet all of the join criteria.

I don't know if this is the issue, but when I first started working with MySQL and Fulltext indices, I often had issues with "stopwords" (http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html) and minimum word lengths (http://www.devcha.com/2008/03/display-mysql-fulltext-search-settings.html). Sometimes, the fulltext search would just ignore certain terms because they were on the stopword list, or they were shorter than the min word length.

There was also another issue where a standard fulltext search wouldn't return ANYTHING if more than 50% of the rows in my table met the criteria for the search. Switching to a boolean search mode solved the >50% problem, but not the stopword/min length issue.

I had to create a fallback %LIKE% search for the fulltext stuff. Possibly not the best way to go, but it at least returned valid results when the fulltext search didn't.

Microsoft SQL Server may be different, but I hope this helps a bit!