以經典 pubs 資料庫為例,示範透過系統預存程序建立全文索引,並使用 CONTAINS 謂詞進行語意搜尋的完整步驟。
EXEC sp_fulltext_database 'enable';EXEC sp_fulltext_catalog 'FT_pubs', 'create';EXEC sp_fulltext_table 'titles', 'create', 'FT_pubs', 'UPKCL_titleidind';EXEC sp_fulltext_column 'titles', 'title', 'add'; EXEC sp_fulltext_column 'titles', 'notes', 'add';EXEC sp_fulltext_table 'titles', 'activate';EXEC sp_fulltext_catalog 'FT_pubs', 'start_full';USE pubs;
GO
IF DATABASEPROPERTY('pubs','IsFulltextEnabled') = 0
EXEC sp_fulltext_database 'enable';
EXEC sp_fulltext_catalog 'FT_pubs', 'create';
EXEC sp_fulltext_table 'titles', 'create', 'FT_pubs', 'UPKCL_titleidind';
EXEC sp_fulltext_column 'titles', 'title', 'add';
EXEC sp_fulltext_column 'titles', 'notes', 'add';
EXEC sp_fulltext_table 'titles', 'activate';
EXEC sp_fulltext_catalog 'FT_pubs', 'start_full';
GO
-- 等待填充完成
WHILE FULLTEXTCATALOGPROPERTY('FT_pubs','PopulateStatus') <> 0
WAITFOR DELAY '00:00:30';
-- 全文搜尋範例
SELECT title
FROM titles
WHERE CONTAINS(title, 'database')
OR CONTAINS(title, 'computer')
OR CONTAINS(notes, 'database')
OR CONTAINS(notes, 'computer');
| 程序 | 權限 | 用途 |
|---|---|---|
| sp_fulltext_service | serveradmin/sysadmin | 設定全文搜尋屬性(斷詞器、噪音詞等) |
| sp_fulltext_catalog | db_owner | 建立/刪除目錄、啟動/停止填充 |
| sp_fulltext_database | db_owner | 啟用/停用資料庫全文功能 |
| sp_fulltext_table | db_ddladmin/db_owner | 註冊/移除全文索引表、啟用/停用 |
| sp_fulltext_column | db_ddladmin | 加入/移除索引欄位 |
CREATE FULLTEXT CATALOG FT_pubs AS DEFAULT;
CREATE FULLTEXT INDEX ON titles(title, notes) KEY INDEX UPKCL_titleidind ON FT_pubs;
ALTER FULLTEXT INDEX ON titles START FULL POPULATION;
DDL 版本更易讀、支援更多選項(斷詞器、停用詞表、語言指定等),新專案請優先採用。