SQL Server FREETEXTTABLE not returning result

Question

I have used SQL Server FREETEXTTABLE function to search in a table column based on the user entered words like a search engine and return best matching rows.

Table column will contain many questions and user will type something in textbox (in any order) and based on what he has typed I need to auto populate the search page.

I have used FREETEXTTABLE for it. But it's not working in some cases.

If I type 'what' it does not return anything.

DECLARE @query VARCHAR(50) = 'what'

SELECT TOP 10 Questions
FROM tblQuestion tq 
INNER JOIN FREETEXTTABLE(tblQuestion, Questions, @query) ft ON (tq.ID = ft.[Key])
ORDER BY ft.Rank DESC

but if I type 'what is' it returns the 10 records.

DECLARE @query VARCHAR(50) = 'what is'

SELECT TOP 10 Questions
FROM tblQuestion tq 
INNER JOIN FREETEXTTABLE(tblQuestion, Questions, @query) ft ON (tq.ID = ft.[Key])
ORDER BY ft.Rank DESC

I also tried CONTAINS and FREETEXT.

SELECT * 
FROM tblQuestion 
WHERE FREETEXT (Questions, 'what')

Even this query returned zero rows.

But this below query returned few rows.

SELECT * 
FROM tblQuestion  
WHERE FREETEXT (Questions, 'what is')

Answer 1

You are probably the victim of stop lists. Make sure all words are included in index with following query:

SELECT * FROM sys.dm_fts_index_keywords(DB_ID('YourDB'), OBJECT_ID('tblQuestion'))

Note that:

A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-text index since they are known to be useless to a search.

If you want to include all words, even those marked as useless, use following code:

ALTER FULLTEXT INDEX ON tblQuestion SET STOPLIST = OFF