SQL Server 2005 全文檢索完整實作指南：從啟用、建立目錄到 CONTAINS/FREETEXT 查詢進階技巧

啟用全文檢索服務

-- 1. 資料庫層級啟用 (SQL 2005 需執行，2008+ 預設啟用)
EXEC sp_fulltext_database 'enable';
GO

建立全文檢索目錄

-- 2. 建立目錄 (屬於資料庫，非檔案組)
CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;
GO
-- 或指定路徑 (需服務帳號寫入權限)
CREATE FULLTEXT CATALOG FTCatalog
ON FILEGROUP [PRIMARY]
IN PATH 'D:\FTData\FTCatalog';
GO

建立全文檢索索引

-- 3. 前提：基礎表必須有「單一、唯一、非空」的索引 (通常是 PK)
-- 假設表 t_commodity 有 PK_Commodity (id INT PRIMARY KEY)

CREATE FULLTEXT INDEX ON dbo.t_commodity
(
    Description LANGUAGE 1028,  -- 繁體中文 LCID
    Specifications LANGUAGE 1028,
    Keywords LANGUAGE 1028
)
KEY INDEX PK_Commodity
ON FTCatalog
WITH CHANGE_TRACKING AUTO;  -- 自動追蹤變更
GO

關鍵點：

• `KEY INDEX` 必須指定**已存在的唯一索引名稱** (非欄位名)。
• `LANGUAGE` 指定斷詞器：1028=繁體中文、2052=簡體中文、1033=英文、1041=日文。
• `CHANGE_TRACKING`：`AUTO` (非同步)、`MANUAL` (手動 `START FULLTEXT INDEX POPULATION`)、`OFF` (不追蹤)。

全文檢索查詢語法

CONTAINS - 精確/前綴/鄰近/權重搜尋

-- 簡單詞彙
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記型電腦');

-- 邏輯運算 (AND / OR / AND NOT)
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記型 AND 電腦');
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記型 OR 平板');
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記型 AND NOT 二手');

-- 前綴搜尋 (* 代表任意字尾)
SELECT * FROM t_commodity WHERE CONTAINS(Description, '"筆記*"');  -- 筆記型、筆記本、筆記...

-- 鄰近搜尋 (NEAR / ~)
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記 NEAR 電腦');
SELECT * FROM t_commodity WHERE CONTAINS(Description, '筆記 ~ 電腦');

-- 權重搜尋 (ISABOUT / WEIGHT)
SELECT *, RANK = FREETEXTTABLE(t_commodity, Description, 'ISABOUT (筆記 WEIGHT(0.8), 電腦 WEIGHT(0.5))') 
FROM t_commodity
WHERE CONTAINS(Description, 'ISABOUT (筆記 WEIGHT(0.8), 電腦 WEIGHT(0.5))')
ORDER BY RANK DESC;

FREETEXT - 自然語言/語意搜尋

-- 自動斷詞、同義詞擴展、語幹分析
SELECT * FROM t_commodity WHERE FREETEXT(Description, '我想買一台好用的筆電');
-- 等效於搜尋：筆記型、電腦、筆電、Notebook、Laptop 等相關詞

CONTAINSTABLE / FREETEXTTABLE - 取得排名分數

-- 關聯排名表，可用於分頁、排序
SELECT t.*, ft.TblRank
FROM t_commodity t
INNER JOIN CONTAINSTABLE(t_commodity, Description, '筆記型 電腦') ft
    ON t.id = ft.[KEY]
ORDER BY ft.TblRank DESC;

維護與監控

填滿/更新索引

-- 完整填滿 (重建)
ALTER FULLTEXT INDEX ON t_commodity START FULL POPULATION;

-- 增量填滿 (手動追蹤模式)
ALTER FULLTEXT INDEX ON t_commodity START INCREMENTAL POPULATION;

-- 更新統計 (優化查詢計畫)
ALTER FULLTEXT INDEX ON t_commodity START UPDATE POPULATION;

監控填滿狀態

SELECT * FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('t_commodity');
-- 或
SELECT * FROM sys.dm_fts_index_population WHERE database_id = DB_ID();

停用/移除

DROP FULLTEXT INDEX ON t_commodity;
DROP FULLTEXT CATALOG FTCatalog;

效能優化建議

• 將全文檢索目錄放置 **獨立磁碟/SSD** (非資料/記錄檔磁碟)。
• 大量匯入資料時：先 `CHANGE_TRACKING OFF` / `DISABLE` → 匯入 → `ENABLE` / `START FULL POPULATION`。
• 斷詞器選擇：中文環境建議安裝 **Microsoft 斷詞器增強包** 或第三方 (如 Jieba CLR) 以提升分詞準確度。
• 查詢時優先使用 `CONTAINS` (精確) 而非 `FREETEXT` (模糊)，並配合 `TOP n` 限制結果集。
• 定期 `ALTER FULLTEXT CATALOG ... REORGANIZE` 整理碎片。

常見問題排查