3

I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine.

I found out that using words which are usually in a stop list (like articles) disrupts or breaks the retrieval in a FullText search and return no results. Using only the important keywords returns the result as usual. AFAIK in older versions of SQL Server a different system than stop list were used. Although I can not find anything relevant in the FTData folder because it is either empty or commented.

On inspecting the database via SQL Server Management Studio I see that no stop list is configured in the selected database (under Storage). I tried adding a new one based on the system stop list but it made no difference to the behavior.

What can cause this behavior?

UPDATE

The used query:

SELECT ProductID FROM SearchTable WHERE CONTAINS(*, '"word"') AND
CONTAINS(*, '"noiseword"')

The results fail because in the search query the words are connected with AND. Using a noise word apparently returns always false so no item is returned.

I tried to change that behavior but it is not prevented by this:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO

I will try a rebuild which may help based on this and this article, as suggested by wBob.

3
  • 1
    You might be better off dropping the existing full-text index and rebuilding from scratch in 2014 to see if you get a different behaviour. You should also try running some tests with sys.dm_fts_parser eg something like this: SELECT * FROM sys.dm_fts_parser( '"test a n noise the word"', 1033, 0, 0 )
    – wBob
    Apr 12, 2016 at 15:08
  • I see that only English words are filtered, however the used language is German. Can I change that? Apr 12, 2016 at 18:29
  • LANGUAGE is one of the arguments of the CREATE FULLTEXT INDEX command. I suggest you have a good read over this article.
    – wBob
    Apr 12, 2016 at 22:05

1 Answer 1

5

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.

Use database; 
ALTER FULLTEXT INDEX ON table
   SET STOPLIST OFF;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.