SQL Server全文索引CONTAINS查询结果不稳定及大小写匹配问题咨询

一、稳定获取全部匹配结果的方法 #

1. 先确认全文索引填充状态 #

直接用以下SQL查询索引的填充进度，确认是否完成：

SELECT 
    OBJECT_NAME(fi.object_id) AS TableName,
    fi.name AS FullTextIndexName,
    fsc.status_desc AS PopulationStatus,
    fsc.completion_type_desc AS CompletionStatus,
    fsc.progress AS PopulationProgress
FROM 
    sys.fulltext_indexes fi
JOIN 
    sys.fulltext_catalogs fc ON fi.fulltext_catalog_id = fc.fulltext_catalog_id
JOIN 
    sys.dm_fts_index_population fsc ON fi.object_id = fsc.table_id
WHERE 
    OBJECT_NAME(fi.object_id) = 'ABSTRACT_table';

当PopulationStatus显示为IDLE且CompletionStatus为SUCCESSFUL时，说明所有数据已完成索引。

2. 手动触发完整填充（若自动填充过慢） #

如果自动填充进度拖沓，执行以下语句强制启动完整填充：

ALTER FULLTEXT INDEX ON ABSTRACT_table START FULL POPULATION;

执行后再用上面的查询监控进度，直到填充完成。

3. 仅在索引就绪后执行查询 #

索引填充过程中，数据是分批加入索引的，因此每次查询结果会存在差异。填充完成后，CHANGE_TRACKING AUTO会自动跟踪后续数据变更，增量填充实时生效，之后的查询结果就会稳定。

二、大小写匹配异常的原因 #

数据库的区分大小写（CS）排序规则不影响全文检索的大小写逻辑，具体原因：

• 全文索引创建时，会按照指定语言（这里是英式英语LCID 2057）做标准化处理，包括将所有文本统一转换为固定大小写后建立索引，目的是实现自然语言的模糊匹配，默认忽略大小写差异。
• CONTAINS的短语匹配（带双引号的精确短语），匹配的是索引中已标准化的词条，而非原始文本的大小写。因此即使数据库是CS排序，全文查询依然会返回所有大小写形式的匹配结果。
• 如果必须强制区分大小写匹配，SQL Server没有直接配置项，只能在全文匹配后加一层LIKE过滤，但这种方式会大幅降低查询性能（1亿条数据的表不推荐），示例语句如下：

  SELECT * FROM ABSTRACT_table
  WHERE CONTAINS(ABSTRACT, '"machine learning"')
  AND ABSTRACT LIKE '%machine learning%';
  

内容的提问来源于stack exchange，提问作者mlee_jordan