SQL Server 2005 全文檢索建立與管理完整指南

全文檢索架構概念

• 全文目錄：儲存全文索引的容器，對應檔案系統目錄。
• 全文索引：建立在資料表特定文字欄位上，支援詞幹斷詞、同義字、鄰近搜尋。
• 斷詞器：依語言切分文字（中文需安裝中文斷詞器）。
• 填補：將資料表資料建立索引的過程，分完整填補、累加填補、自動填補。

T-SQL 快速建立腳本

-- 1. 啟用資料庫全文檢索
EXEC sp_fulltext_database 'enable';

-- 2. 建立全文目錄
EXEC sp_fulltext_catalog 'FT_ProductCatalog', 'create';

-- 3. 建立全文索引（需資料表有唯一索引/主鍵）
EXEC sp_fulltext_table 'Products', 'create', 'FT_ProductCatalog', 'PK_Products';

-- 4. 加入欲索引欄位
EXEC sp_fulltext_column 'Products', 'ProductName', 'add';
EXEC sp_fulltext_column 'Products', 'Description', 'add';

-- 5. 啟用全文索引
EXEC sp_fulltext_table 'Products', 'activate';

-- 6. 啟動完整填補
EXEC sp_fulltext_catalog 'FT_ProductCatalog', 'start_full';

-- 7. 查詢填補狀態
SELECT FULLTEXTCATALOGPROPERTY('FT_ProductCatalog', 'PopulateStatus');
-- 0=閒置, 1=完整填補中, 2=累加填補中, 3=暫停, 4=節流, 5=復原, 6=關閉

SSMS 圖形介面建立步驟 (圖文流程)

1. 資料表右鍵 → 全文索引 → 定義全文索引... 啟動精靈。
2. 選擇 唯一索引鍵（必須為單欄、非 NULL、唯一，通常是 PK）。
3. 勾選要全文索引的文字欄位（char/varchar/nchar/nvarchar/text/ntext/xml/varbinary），指定語言（中文選 Chinese (Simplified/Traditional)）。
4. 選擇/建立 全文目錄，設定目錄路徑（建議非系統碟、SSD）。
5. 設定 填補排程：
   - 自動追蹤變更（背景自動累加，近即時）。
   - 排程完整填補（如每週日凌晨）。
   - 排程累加填補（如每小時）。
6. 完成精靈，SSMS 自動建立目錄、索引、啟用、啟動首次完整填補。

全文搜尋查詢語法

常見問題與維護

• 中文不斷詞：確認安裝 Microsoft 簡體/繁體中文斷詞器（SQL 2005 需額外下載），欄位語言設正確。
• 填補卡住/失敗：檢查 SQL Server 錯誤記錄檔、全文檢索記錄檔（LOG 目錄下 SQLFT*）、磁碟空間、目錄權限（服務帳號需修改權限）。
• 索引碎片：定期執行 ALTER FULLTEXT CATALOG FT_ProductCatalog REORGANIZE;（合併碎片）或 REBUILD（重建）。
• 升級/移轉：全文目錄**無法**直接備份/還原，需在目標重建索引並填補。

效能調優建議

1. 僅索引必要文字欄位，避免大型 ntext/image 欄位。
2. 啟用 自動追蹤變更 替代頻繁完整填補。
3. 全文目錄放置於獨立高速磁碟（SSD/NVMe）。
4. 查詢加入 OPTIMIZE FOR、限制 TOP N、使用 CONTAINSTABLE 取得 RANK 排序而非 CONTAINS 當 WHERE 條件。
5. 監控 sys.dm_fts_index_population、sys.fulltext_catalogs 狀態。

總結

SQL Server 2005 全文檢索提供強大的非結構化文字搜尋能力。建立流程：啟用資料庫 → 建立目錄 → 定義索引(唯一鍵+文字欄位+語言) → 啟用 → 填補。查詢首選 CONTAINS/CONTAINSTABLE 精確控制，FREETEXT 適合模糊搜尋。維護重點在斷詞器語言正確、填補排程合理、目錄碎片定期整理、磁碟 I/O 隔離。