CODEWITHSUNDEEP
sql-serverfull-text-searchfreetextcontainstablefreetexttable
Jacques
Jacques

Reputation: 7135

SQL Server FTS indexing correct keywords but not returning results with those keywords

I've created a FTS catalog which indexes the Title column of a table called Articles. The word breaker language is set to Dutch.

The article title is "Contactgegevens Wijkteams 2019". My search term is 'contactgegevens' which is Dutch for 'Contact details'. This word could potentially be split into 'contact' and 'gegevens', although I have checked the indexed keywords which has successfully indexed the full word from the correct table/column.

Search term:

declare @searchTerm nvarchar(100) 
select @searchTerm = 'contactgegevens';

Using Freetext:

If I use FREETEXT in the where clause I do find the result but it comes up near the end of around 300 rows. The majority of the rows don't have this word in the title column, not even words close in meaning. 

SELECT a.ArticleID, a.Title
FROM Articles a
WHERE
    FREETEXT(a.Title, @searchTerm))

Using FreeTextTable:

With FREETEXTTABLE, I get far less results but not one of them contain the keyword:'contactgegevens' or variations of it. 

select * 
from 
    freetexttable(Articles, Title, @search, LANGUAGE N'Dutch', 100) as key_table
inner join 
    Articles a on a.ArticleID = key_table.[Key]
order by 
    key_table.RANK desc

Using ContainsTable:

CONTAINSTABLE seems to return very similar results to FREETEXTTABLE. 

SELECT key_table.rank, a.*
FROM Articles a
INNER JOIN
    CONTAINSTABLE(Articles, Title, @searchTerm, LANGUAGE N'Dutch', 100) AS key_table on key_table.[KEY] = a.ArticleID 
WHERE
ORDER BY 
    key_table.rank DESC

As mentioned I've checked the indexed keywords using the following query: 

select * 
from sys.dm_fts_index_keywords(DB_ID('MyDatabase'), OBJECT_ID('Articles'))
where (display_term like 'contactgegevens%') and column_id = 3
order by display_term

and the keyword is indexed for the correct table/column and looking at records close to this result I can see it's indexed other words relevant to the article title I'm looking for.

I'm expecting to be able to do a search for a phrase such as "Contactgegevens Wijkteams 2019" and have the article with that exact title appear at the top of the list, but it doesn't. In some cases it doesn't appear in the search results at all.

What am I missing here?

Upvotes: 0

Views: 105

Answers (1)

Jacques
Jacques

Reputation: 7135

Turns out it was a simple mistake on my part. My join was using the ArticleID instead of the ArticleVersionID which is what the Catalog uses as its unique key and what is represented by key_table.[KEY]. 

SELECT key_table.rank, a.*
FROM Articles a
INNER JOIN
    CONTAINSTABLE(Articles, Title, @searchTerm, LANGUAGE N'Dutch', 100) AS key_table on key_table.[KEY] = a.ArticleVersionID 
WHERE
ORDER BY 
    key_table.rank DESC

Upvotes: 0

Related Questions