fulltext index (CONTAINS) does not return all results

Ali Abdulhay 20 Reputation points
2023-04-14T07:28:37.3466667+00:00

Among more than 100 million text (abstracts of publications) indexed in my db (microsoft sql) table, I want to fetch all those abstracts containing the phrase 'machine learning'. To do so, I have created the full text as below:

CREATE FULLTEXT CATALOG ftCatalogForAbstract AS DEFAULT;

CREATE UNIQUE NONCLUSTERED INDEX IX_ABSTRACT 
ON ABSTRACT_table (ID)

CREATE FULLTEXT INDEX ON ABSTRACT_table
(
    ABSTRACT                      --Full-text index column name 
    Language 2057                 --2057 is the LCID for British English
)
KEY INDEX IX_ABSTRACT ON ftCatalogForAbstract
WITH CHANGE_TRACKING AUTO

now I can query:

SELECT * FROM ABSTRACT_table
WHERE CONTAINS(ABSTRACT, '"machine learning"')

Whenever I run the query, I always get a different result set (sometimes 19K rows, sometimes 43K rows...). So is there a way to get all the results in a stable way?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,853 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 41,006 Reputation points
    2023-04-14T07:56:18.4433333+00:00

    I never had this case of raying result. The only thing I can image is, that there are large updates on the base table; a full-text index get updates asynchron in background, so that it can take some time until new/change data appears in a search result.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful