SQL Server 2008 FTS CONTAINSTABLE Not Returning More Than Five Rows

Question

I have a single table called "Indexes", it contains one nvarchar and three ntext columns (all Full Text Indexes). Index is up to date.

CONTAINSTABLE(Indexes, *), 'test', 5) //5 results

No matter what I change the above keyword too, it only returns the first 3-5 results. It should roughly return 90-120 results, for the above query.

SELECT count(*) FROM Indexes WHERE [Description] like '%test%' //122 results

How would I start to troubleshoot this problem?

Answer 1

Your CONTAINSTABLE has the top_n_by_rank parameter set to 5

You'll never get more then 5 rows with this... the comment //5 results even mentions it..

You should use CONTAINSTABLE(Indexes, *), 'test')