Using SQL Server 2016.
I have a table in a database that contains products that I search through "keywords" with Full Text Search. Our client has asked that we remove the single character "n" from the stop list to allow for searching on terms like "fish n chips" (not a real example).
However, after altering the stop list and rebuilding the index I am seeing unexpected results, in particular a rank of zero:
SELECT * FROM CONTAINSTABLE (dbo.Products, Keywords, ' "no*"')
SELECT * FROM CONTAINSTABLE (dbo.Products, Keywords, ' "n*"')
Gives me results of:
As you can see I am getting a rank of 0 for all results. I have tried this with other characters and I get RANK as expected. I have checked the stop lists via:
SELECT * FROM sys.fulltext_stoplists
SELECT * FROM sys.fulltext_stopwords
WHERE stoplist_id = 5 AND language_id = 1033
In general I can see no reason for having a rank of 0 returned. What has confused me more is that I have a second database on the same server using the same indexing and stop list, and its not an issue.
I have been looking into this and seen people having this issue around FREETEXTTABLE however the issue here is not down to lack of data.
If anyone has any idea on things I can check to help track this down it would be gratefully received.
