sql-serverfull-text-searchstop-wordssql-server-2012-express

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

  • 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. :)