Full-Text search stoplist not working with filetables

Question

Hi,

I'm trying to use my own stop word list with full text indexes for files saved in filetables.
I'm using SQL Server 2019 Standard.

But it seems that my stoplist is not working with FT-indexes on filetables. If I create an FT-index for a normal table and use the same stoplist in there it works just fine.

I've tried to search FT documentation for any reference that stoplist would not work with filetables but found none. By googling I found couple of really old cases having the same problem.

Does anyone know if it actually is the case that you can't use stoplist for FT-index with filetables?

Answer 1

Did some more investigation and seems that stoplist implementation under the hood works differently for normal tables and filetables.

When using stoplist with normal tables the stopwords are left out of the FT-index, so when using the sys.dm_fts_index_keywords_by_document function or CONTAINS predicate the stopwords are not returned.

But when using stoplist with filetables all words are entered in to the FT-index, regardless if they exist in the stoplist or not, and are also returned with sys.dm_fts_index_keywords_by_document function. But if you use CONTAINS predicate the stoplist words are not returned.

My implementation uses a JOIN to sys.dm_fts_index_keywords_by_document and that is why stoplist failed to work with FT-indexes on filetables.