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.