SQL Server 全文索引填充策略與定時計劃、噪音詞過濾導致數字搜索失效的排查實戰

三種填充模式

操作入口（SSMS）

• 表級：右鍵表 → 全文索引 → 啟動完全/增量填充、手動/自動跟蹤更改、應用跟蹤的更改
• 目錄級：對象資源管理器 → 數據庫 → 存儲 → 全文目錄 → 右鍵 → 全部重新生成

定時填充計劃配置

1. 表/目錄右鍵 → 屬性 → 計劃/填充計劃 → 新建
2. 計劃類型：SQL Server Agent 啟動時 / CPU 空閒時 / 執行一次 / 重復執行（每天/周/月）
3. 勾選「已啟用」，設置持續時間（可無結束日期）
4. 確定後選擇填充方式（完全/增量/更改跟蹤）

報錯「添加計劃失敗」：需先安裝 SP1 再裝 累積更新 9.0.2153（KB918222）。

實戰疑難：搜索「4」返回空，搜「40」有結果

現象：CONTAINS(標准地址, '4') 空集，CONTAINS(..., '40') 有數萬條。

根因：全文引擎將單個數字 0-9 視為噪音詞自動過濾。

解決：編輯噪音詞文件，刪除 0-9 相關行：
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseCHS.txt
（中文環境；英文對應 noiseENG.txt）

修改後需重新完全填充該全文索引生效。

系統存儲過程速查（SQL 2000/2005 兼容）

-- 啟用數據庫全文
EXEC sp_fulltext_database 'enable';

-- 創建/刪除目錄
EXEC sp_fulltext_catalog 'gf_ft', 'create';
EXEC sp_fulltext_catalog 'gf_ft', 'drop';

-- 表級：創建/激活/填充
EXEC sp_fulltext_table 'product', 'create', 'gf_ft', 'pk_product';
EXEC sp_fulltext_column 'product', 'productname', 'add';
EXEC sp_fulltext_table 'product', 'activate';
EXEC sp_fulltext_table 'product', 'start_full';

-- 等待填充完成
WHILE FULLTEXTCATALOGPROPERTY('gf_ft', 'populateStatus') <> 0
  WAITFOR DELAY '0:0:5';

-- 查詢
SELECT * FROM product WHERE CONTAINS(productname, '"美贊臣"');

-- 卸載
EXEC sp_fulltext_table 'product', 'deactivate';
EXEC sp_fulltext_column 'product', 'productname', 'drop';
EXEC sp_fulltext_table 'product', 'drop';
EXEC sp_fulltext_catalog 'gf_ft', 'stop';
EXEC sp_fulltext_catalog 'gf_ft', 'drop';

SQL 2005+ 新語法（推薦）

-- 創建目錄
CREATE FULLTEXT CATALOG ft_catalog;

-- 創建索引（需唯一索引作 KEY INDEX）
CREATE FULLTEXT INDEX ON product(productname) 
  KEY INDEX pk_product ON ft_catalog;

-- 查詢
SELECT * FROM product WHERE CONTAINS(productname, '周傑倫 OR jay');
SELECT * FROM product WHERE FREETEXT(productname, '數碼攝像機');

最佳實踐

• 大表首選增量填充 + 定時計劃（需 timestamp 列）
• 高實時性業務用自動更改跟蹤，接受少量資源佔用
• 定期重建目錄碎片整理（維護窗口執行）
• 噪音詞文件按業務定制（如保留數字、特定縮寫）
• 全文索引列盡量窄、精，避免大字段全量索引