fulltext index (CONTAINS) does not return all results and always cuts

43 views Asked by At

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

and 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...). If I am not mistaken, it is explained here link

So is there a way to get all the results in a stable way? In addition, I see that my query returns the abstracts containing 'Machine learning' though the collation is CS. Have I missed something when creating the fulltext index?

EDIT: Apparently I was too impatient. When I ran the create full index query it finished immediately which confused me. But it had to take some time to complete the index I guess. So after the weekend passed, when I ran the same search query a few times, I always got the same reults.

0

There are 0 answers