SQL Server 全文檢索索引完整建立教學 (以 pubs 為例)

全文檢索服務讓 SQL Server 能對大量文字資料進行語意搜尋 (CONTAINS/FREETEXT)。本文以 pubs 資料庫的 titles 表為例，詳細說明使用系統預存程序建立全文索引的六大步驟與查詢範例。

六大建立步驟

1. 啟用資料庫全文功能：EXEC sp_fulltext_database 'enable'
2. 建立全文目錄：EXEC sp_fulltext_catalog 'FT_Pubs', 'create'
3. 註冊資料表：EXEC sp_fulltext_table 'titles', 'create', 'FT_Pubs', 'UPKCL_titleidind' (需指定唯一索引鍵)
4. 加入索引欄位：EXEC sp_fulltext_column 'titles', 'title', 'add' / EXEC sp_fulltext_column 'titles', 'notes', 'add'
5. 啟用資料表全文索引：EXEC sp_fulltext_table 'titles', 'activate'
6. 填補全文目錄：EXEC sp_fulltext_catalog 'FT_Pubs', 'start_full' (可搭配 FULLTEXTCATALOGPROPERTY 監控進度)

完整 T-SQL 腳本

USE pubs; GO

-- 1. 啟用全文功能
IF (SELECT DATABASEPROPERTY('pubs', 'IsFulltextEnabled')) = 0
    EXEC sp_fulltext_database 'enable';

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

-- 3. 註冊 titles 表 (UPKCL_titleidind 為主鍵索引名)
EXEC sp_fulltext_table 'titles', 'create', 'FT_Pubs', 'UPKCL_titleidind';

-- 4. 指定索引欄位
EXEC sp_fulltext_column 'titles', 'title', 'add';
EXEC sp_fulltext_column 'titles', 'notes', 'add';

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

-- 6. 完整填補
EXEC sp_fulltext_catalog 'FT_Pubs', 'start_full';

-- 等待填補完成
WHILE FULLTEXTCATALOGPROPERTY('FT_Pubs', 'PopulateStatus') <> 0
    WAITFOR DELAY '00:00:30';

-- 7. 全文查詢範例
SELECT title FROM titles
WHERE CONTAINS(title, 'database') 
   OR CONTAINS(title, 'computer')
   OR CONTAINS(notes, 'database')
   OR CONTAINS(notes, 'computer');

核心系統預存程序速查表

進階應用提示

• 增量填補：EXEC sp_fulltext_catalog 'FT_Pubs', 'start_incremental' 僅處理變更資料，適合固定排程。
• 變更追蹤：建立索引時指定 WITH CHANGE_TRACKING AUTO (SQL 2005+ 新語法) 自動維護。
• 斷字器與同義字：安裝對應語言的斷字器；使用 sp_fulltext_service 'update_languages' 更新語言清單。
• 效能調校：將全文目錄置於獨立磁碟；調整 max full-text crawl range 降低 CPU 衝擊。

注意：SQL Server 2005+ 引入新 DDL 語法 (CREATE FULLTEXT CATALOG/INDEX)，建議新專案改用新語法，舊版預存程序僅為相容保留。