Sql Server ContainsTable return zero result when try to select top records

Question

We have implemented ContainsTable in SQL Server. Problem is when we run the query as following

select DATAFILENAME 
from TableName
INNER JOIN CONTAINSTABLE (TableName, BOOLEANTEXT, '("xxxx" )  ') AS KEY_TBL ON DATAFILENAME = KEY_TBL.[KEY]  and CASETYPE like '%xxxx%'

This will return about 1200 records.

But I just want to select top 1000, here is the query as following

select DATAFILENAME 
from TableName
INNER JOIN CONTAINSTABLE (TableName, BOOLEANTEXT, '("xxxx",*1000* )  ') AS KEY_TBL ON DATAFILENAME = KEY_TBL.[KEY]  and CASETYPE like '%xxxx%'** 

This query will return zero records.

I have rebuild full text index, change tracking to auto. But still showing zero result.

Please advice if I am missing some points. Thanks in advance

Answer 1

Your two queries are:

select DATAFILENAME
from TableName INNER JOIN
      CONTAINSTABLE(TableName, BOOLEANTEXT, '("xxxx" ) ')AS KEY_TBL
      ON DATAFILENAME = KEY_TBL.[KEY] and CASETYPE like '%xxxx%'

and:

select DATAFILENAME
from TableName INNER JOIN
      CONTAINSTABLE(TableName, BOOLEANTEXT, '("xxxx" ) ', 1000)AS KEY_TBL
      ON DATAFILENAME = KEY_TBL.[KEY] and CASETYPE like '%xxxx%'

The first returns 1,200 records and the second no records.

Conclusion: The first 1,000 rows returned by CONTAINSTABLE have no match in TableName using the conditions specified.

If you want only 1,000 rows, use top 1000:

select top 1000 DATAFILENAME
from TableName INNER JOIN
      CONTAINSTABLE(TableName, BOOLEANTEXT, '("xxxx" ) ')AS KEY_TBL
      ON DATAFILENAME = KEY_TBL.[KEY] and CASETYPE like '%xxxx%'
order by KEY_TBL.rank desc;

Note the order by KEY_TBL.rank, so the best matching rows will be returned first.