SQL Server 全文檢索完整實作指南

從 LIKE 查詢到全文檢索：效能躍升的完整實作

當資料量增長至百萬級，LIKE '%關鍵字%' 將導致全表掃描、效能崩潰。SQL Server 全文檢索 (Full-Text Search) 以倒排索引換取空間，實現毫秒級關鍵字搜尋。本文以實戰案例完整示範啟用、建立、填充與查詢流程。

環境準備

USE PopAsk
GO
-- 1. 啟用資料庫全文檢索功能
EXEC sp_fulltext_database 'enable'
GO

建立全文檢索目錄與索引 (以 Question_Total 表為例)

-- 2. 建立全文檢索目錄
EXEC sp_fulltext_catalog 'Q_ID_Desc', 'create'
GO

-- 3. 將表加入目錄，指定唯一鍵索引 (必要條件)
EXEC sp_fulltext_table 'Question_Total', 'create', 'Q_ID_Desc', 'PK_Question_Total'
GO

-- 4. 加入需檢索的欄位
EXEC sp_fulltext_column 'Question_Total', 'Title', 'add'
EXEC sp_fulltext_column 'Question_Total', 'Content', 'add'
GO

-- 5. 啟動完整填充
EXEC sp_fulltext_table 'Question_Total', 'start_full'
GO

-- 6. 監控填充狀態 (1=進行中, 0=完成)
SELECT FULLTEXTCATALOGPROPERTY('Q_ID_Desc', 'PopulateStatus')
GO

第二張表 Answer_Total 同步建立

EXEC sp_fulltext_catalog 'A_ID_Desc', 'create'
EXEC sp_fulltext_table 'Answer_Total', 'create', 'A_ID_Desc', 'PK_Answer_Total'
EXEC sp_fulltext_column 'Answer_Total', 'Answer_Content', 'add'
EXEC sp_fulltext_table 'Answer_Total', 'start_full'
GO

全文檢索查詢實戰

-- FREETEXTTABLE: 自然語言查詢，回傳排名關聯度
SELECT * FROM FREETEXTTABLE(Question_Total, Title, '測試')
SELECT * FROM FREETEXTTABLE(Answer_Total, Answer_Content, '測試')

-- CONTAINSTABLE: 精確詞彙/前綴/近接查詢
SELECT * FROM CONTAINSTABLE(Question_Total, (Title, Content), ' "效能" AND "優化" ')

關鍵維護設定

• 斷詞器設定：中文環境修改 C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServerConfig\noise.chs，建議保留問號 ? 作為單一斷詞符號
• 資料庫定序：設為 Chinese_PRC_CI_AS 確保中文斷詞正確
• 排程增量填充：建立 SQL Agent 作業，每日執行增量填充

  EXEC sp_fulltext_table 'Question_Total', 'start_incremental'
  EXEC sp_fulltext_table 'Answer_Total', 'start_incremental'

遷移提示：SQL Server 2008+ 新語法

新版本採用 DDL 語法 (CREATE FULLTEXT CATALOG, CREATE FULLTEXT INDEX)，舊版預存程序雖相容但建議漸進式遷移。