logo头像

Edward.K Thinking

建立Azure SQL Database 全文搜索功能

自從Azure SQL Database Release 全文檢索功能一直沒有機會去使用,今天剛好遇到需要進行多欄位資料模糊查詢的需求,為了避免使用Like 加 OR 有可能會導致查詢性能下,因此,來使用一下Azure SQL Database功能,以前在地端的SQL Server要做全文檢索,還需要自行去安裝這部分功能,雖然,沒有太難安裝,但似乎也沒有那樣便利,如果在Azure就可以省去這些麻煩,另外,在Azure上面使用全文檢索好處是還不需要花費呢

一開始預設是沒有設定全文搜索,所以,情況會是下面這樣(這當然是廢話囉,沒設定一定會出錯)

所以,開始建立全文檢索吧,這次要建立全文檢索是對應到檢視表,一開始還必須先把檢視表設定好index,要設定檢視表的index必須注意一件事情,就是必須先把檢視表加入WITH SCHEMABINDING,一開始不知道這問題,一直發生錯誤訊息

1
2
3
CREATE VIEW [dbo].[VW_XXXX] WITH SCHEMABINDING
AS
select .....

就可以幫檢視表建立索引了

1
2
3
4
5
 CREATE UNIQUE CLUSTERED INDEX [VW_XXX_idx] ON [dbo].[VW_XXX]
(
[UserID] ASC
)
GO

接下來就是建立全文索引目錄

1
CREATE FULLTEXT CATALOG UserCatalog AS DEFAULT;

建立完畢後,在SSMS看Azure SQL Database就可以看到如下圖這樣

建立檢視表的全文索引,這邊我們要進行全文索引共包含三個欄位,因為建立全文索引,必須要對應到資料表或是檢視表的索引,所以,前面我們必須在檢視表中加入索引,如果是對應到資料表這動作也是要做的

1
2
3
CREATE FULLTEXT INDEX ON VW_XXX 
( NickName,company,jobtitle )
KEY INDEX [VW_XXX_idx] ON UserCatalog;

完畢後,開始啟用索引或是說擴張該資料表或是檢視表的索引

1
2
ALTER FULLTEXT INDEX ON VW_XXX ENABLE;
ALTER FULLTEXT INDEX ON VW_XXX START FULL POPULATION;

建立完成後,可以用下面語法看你建立的全文索引

1
2
SELECT *
FROM sys.dm_fts_index_population;

如果你要看你的資料被斷詞的狀況,可以使用下面這語法

1
2
SELECT *
FROM sys.dm_fts_index_keywords ( DB_ID('DB Name'),OBJECT_ID('VW_XXX')) ;

這樣就可以輕輕鬆鬆擁有全文檢索功能了

上一篇