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.
Best Answer
Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.