Full text search does not work if stop word is included even though stop word list is empty

I would like to be able to search every word so I have cleared the stop word list. Than I have rebuilt the index. But unfortunately if I type in a search expression with stop word in it it still returns no row. If I leave out just the stop word I do get the results. E.g. "double wear stay in place" - no result, "double wear stay place" - I get the results that actually contain "in" as well.

Does anyone know why this can be? I am using SQL Server 2012 Express.

Thanks a lot!

Solution 1:

Meanwhile I have managed to solve the issue. The problem was that I had my own stop list which was indeed empty but my fulltext catalog was associated not with my own stoplist but with the system one. Here are a couple of useful queries for solving stopword and full text search issues:

Query stopwords (does not give back the system ones!):

select * from sys.fulltext_stopwords

Query stop lists (does not give back the system list!):

select * from sys.fulltext_stoplists

Check what words are included in the catalog:

SELECT * FROM sys.dm_fts_index_keywords(DB_ID('dbname'), OBJECT_ID('tablename'))

Check the association:

select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

Turn stoplist off:

ALTER FULLTEXT INDEX ON CremeSearchFT SET STOPLIST = OFF

I hope it helps someone. :)

Solution 2:

Based on my researches, it is related with Full-Text Index Stop-list option, one of the main properties of Full-Text Index. If you set this option to "System", all keywords included in "System Stop list" will not be available for your CONTAINS() clauses and unfortunately there will be no result sets for such cases. Solution;

To set this option to "OFF" which will bypass the stop list check in your language set. For example you in English, sen in Turkish. These are marked as stop words and will make sense for SQL Server Engine to be excluded in such searches, unless you set "system" option. So, do not use "System" option. To do this, pls run the following script on db which your table exists:

ALTER FULLTEXT INDEX ON table_name SET STOPLIST = OFF

To create your own stop list. In this case, you can define your special stop words and can create specific stop lists. So that, only these ones will be treated as they will not make any sense for SQL Server Engine. Once you create it, you can start using this by running the following scripts:

CREATE FULLTEXT STOPLIST myStoplist

GO

ALTER FULLTEXT STOPLIST [myStoplist] ADD 'you' LANGUAGE 'English'

GO

ALTER FULLTEXT INDEX ON table_name SET STOPLIST = [myStoplist]

GO

I hope this helps:) Good luck...

Solution 3:

In case anyone is interested, I fleshed out the check association query in apolka's answer to give more legible results:

--Check the association:
SELECT
    ft_c.name AS [Catalog],
    s.name AS [Schema],
    o.name AS [Table],
    [StopList] =
    CASE
        WHEN ft_i.stoplist_id IS NULL THEN 'None'
        ELSE ISNULL(ft_sl.NAME, 'System')
    END
FROM 
    sys.fulltext_indexes AS ft_i LEFT OUTER JOIN
    sys.fulltext_stoplists AS ft_sl ON ft_sl.stoplist_id = ft_i.stoplist_id INNER JOIN
    sys.fulltext_catalogs AS ft_c ON ft_c.fulltext_catalog_id = ft_i.fulltext_catalog_id INNER JOIN
    sys.objects AS o ON o.object_id = ft_i.object_id INNER JOIN
    sys.schemas AS s ON s.schema_id = o.schema_id

So silly this stuff is missing from SSMS!