question

KiiskiAntti-6503 avatar image
0 Votes"
KiiskiAntti-6503 asked ·

Full-Text search stoplist not working with filetables

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?

sql-server-general
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KiiskiAntti-6503 avatar image
0 Votes"
KiiskiAntti-6503 answered ·

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.

· 2 ·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image ErlandSommarskog ·

Interesting. Unless you are in hope of a better answer, I would suggest that you accept your post as the answer.

0 Votes 0 ·
Cathyji-msft avatar image Cathyji-msft ·

Hi @KiiskiAntti-6503,

Thanks for sharing the information. Suggest you mark your reply as answer. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·