本文將簡單說明如何使用 SQL Server 2012 全文檢索的新功能 - Property Search。
在前面幾篇文章中，筆者介紹了有關 SQL Server 2012 處理非結構化資料新功能 - FileTables，本文將介紹透過全文檢索的 Property Serarch 功能來搜尋儲存在 FileTables 中的檔案屬性。
所謂的 Property 以 Office 文件為例，就是如下圖的標題、作者、標記主旨等資訊。
您也可以在 Office 檔案上按滑鼠右鍵，選擇【內容】來查看檔案的詳細資料。
當您把這些 Office 檔案存放在 FileTables，可以透過全文檢索的 Property Search 搭配作業系統本身的篩選器，來找出符合條件的檔案，所有的動作都可以用單一種查詢語言也就是 T-SQL 來完成。該如何使用 Property Serach，步驟如下：
-
載入作業系統的篩選器和斷辭工具，並重新啟動 FDHost 處理緒。
1: --載入 OS 的篩選器和斷辭工具2: EXEC sp_fulltext_service 'load_os_resources',1
3: GO
4:5: --重新啟動 FDHost 處理序6: EXEC sp_fulltext_service 'restart_all_fdhosts'
7: GO -
建立全文檢索目錄（FULLTEXT CATALOG）。
全文檢索目錄是用來儲存全文檢索資料，您可以從【Object Explorer > Databases > 啟動 FileTables 功能的資料庫（本文以 FTDB 為例）> Storage > Full Text Catalogs】項目上按滑鼠右鍵，選擇【New Full-Text Catalog】（如下圖）以新增全文檢索目錄。
於【New Full-Text Catalog】視窗中輸入下列資訊，本文將【Full-text catalog name】設定為 MyFTCATALOG，【Owner】設定為 dbo，並勾選【Set as default catalog】。
上述動作您也可以透過下列 T-SQL 來達到相同的目的：
1: --建立全文檢索目錄，用來儲存全文檢索資料2: CREATE FULLTEXT CATALOG MyFTCATALOG AS DEFAULT
-
建立屬性清單（Property List）。
這個步驟您必須把將來要搜尋的屬性都建立在屬性清單中，才可以透過全文檢索的屬性搜尋功能來找到特定屬性的文件。您可以從【Object Explorer > Databases > 啟動 FileTables 功能的資料庫（本文以 FTDB 為例）> Storage > Search Property Lists】項目上按滑鼠右鍵，選擇【New Search Property Lists】（如下圖）以新增搜尋屬性清單。
於【New Search Property Lists】視窗中輸入下列資訊，本文將【Name】設定為 pl，【Owner】設定為 dbo
同樣的，您可以以下列 T-SQL 敘述來完成上述建立屬性清單的動作：
1: --建立屬性清單2: CREATE SEARCH PROPERTY LIST pl;
特別提醒的是，建立屬性清單時，必須於敘述的最後加上分號，否則會遇到如下列的錯誤訊息：
1: Msg 10740, Level 15, State 1, Line 1
2: A search property list statement must end with a semicolon (;).
-
建立搜尋屬性。
有了屬性清單之後，接著必須設定您要針對那些屬性進行搜尋。您可以從【Object Explorer > Databases > 啟動 FileTables 功能的資料庫（本文以 FTDB 為例）> Storage > Search Property Lists > 您的屬性清單（本文以 pl 為例）】項目上按滑鼠右鍵，選擇【Properties】（如下圖）以建立搜尋屬性。
在此您必須輸入【Property Name】、【Property Set Guid】、【Property Int ID】及【Property Description】等資訊，其中【Property Name】是用來使用全文檢索時的屬性名稱，您可以依照您的需求來設定容易辨識的名稱，並在【Property Description】欄位中輸入屬性的描述
再來是最重要的【Property Set Guid】和【Property Int ID】（上圖的 2 和 3 處），有關這兩個屬性必須根據 MSDN 上的定義來輸入。
當然您也可以透過下列 T-SQL 來完成如上圖的設定：
1: --新增要搜尋的屬性2: ALTER SEARCH PROPERTY LIST pl
3: ADD 'Title'
4: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
5: PROPERTY_DESCRIPTION = '標題' );
6:7: ALTER SEARCH PROPERTY LIST pl
8: ADD 'Author'
9: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4,
10: PROPERTY_DESCRIPTION = '作者' );
11:12: ALTER SEARCH PROPERTY LIST pl
13: ADD 'Tags'
14: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5,
15: PROPERTY_DESCRIPTION = '關鍵字' );
特別提醒的是，建立搜尋屬性時，必須於敘述的最後加上分號，否則會遇到如下列的錯誤訊息：
1: Msg 10740, Level 15, State 1, Line 1
2: A search property list statement must end with a semicolon (;).
-
建立全文檢索索引（Full-Text Index）。
您可以透過下列 T-SQL 來建立全文檢索索引：
1: --建立全文檢索索引2: CREATE FULLTEXT INDEX ON MyFileTable
3: (file_stream TYPE COLUMN file_type)
4: KEY INDEX PK__MyFileTa__5A5B77D57B613748
5: WITH SEARCH PROPERTY LIST = pl
6: GO
上述第四列的 PK 是 MyFileTable 這個資料表的 Primary Key，您可以透過下列 T-SQL 來查詢 FileTable 的 Primary Key：
1: select name
2: from sys.objects
3: where type='PK'
4: and parent_object_id
5: = (6: select object_id
7: from sys.objects
8: where type = 'U'
9: and name = 'MyFileTable'
10: ) -
利用 CONTAINS 函式進行屬性搜尋。
1: --查詢上述建立文件屬性2:3: --搜尋作者屬性包含 Terry 的檔案。4: SELECT* FROM MyFileTable
5: WHERE CONTAINS(PROPERTY(file_stream,'Author'),'terry')
6:7: --搜尋標題屬性為【我的Excel文件】的檔案。8: SELECT* FROM MyFileTable
9: WHERE CONTAINS(PROPERTY(file_stream,'Title'),'我的Excel文件')
10:11: --搜尋關鍵字為【pptx】的檔案。12: SELECT* FROM MyFileTable
13: WHERE CONTAINS(PROPERTY(file_stream,'Tags'),'pptx')
執行結果：
【完整程式碼】
1: use FTDB
2: go
3:
4: --Step 1、變更 SQL Server 全文檢索搜尋的伺服器屬性
5:
6: --載入 OS 的篩選器和斷辭工具
7: EXEC sp_fulltext_service 'load_os_resources',1
8: GO
9:
10: --重新啟動 FDHost 處理序
11: EXEC sp_fulltext_service 'restart_all_fdhosts'
12: GO
13:
14: --Step 2
15: --建立全文檢索目錄，用來儲存全文檢索資料
16: CREATE FULLTEXT CATALOG MyFTCATALOG AS DEFAULT
17:
18: --Step 3
19: --建立屬性清單
20: CREATE SEARCH PROPERTY LIST pl;
21:
22: --Step 4
23: --新增要搜尋的屬性
24: ALTER SEARCH PROPERTY LIST pl
25: ADD 'Title'
26: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
27: PROPERTY_DESCRIPTION = '標題' );
28:
29: ALTER SEARCH PROPERTY LIST pl
30: ADD 'Author'
31: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4,
32: PROPERTY_DESCRIPTION = '作者' );
33:
34: ALTER SEARCH PROPERTY LIST pl
35: ADD 'Tags'
36: WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5,
37: PROPERTY_DESCRIPTION = '關鍵字' );
38:
39: --Step 5
40:
41: --查詢 PK 的名稱
42: select name
43: from sys.objects
44: where type='PK'
45: and parent_object_id
46: = (
47: select object_id
48: from sys.objects
49: where type = 'U'
50: and name = 'MyFileTable'
51: )
52:
53: --建立全文檢索索引
54: CREATE FULLTEXT INDEX ON MyFileTable
55: (file_stream TYPE COLUMN file_type)
56: KEY INDEX PK__MyFileTa__5A5B77D57B613748
57: WITH SEARCH PROPERTY LIST = pl
58: GO
59:
60:
61: --Step 6
62: --查詢上述建立文件屬性
63:
64: --搜尋作者屬性包含 Terry 的檔案。
65: SELECT* FROM MyFileTable
66: WHERE CONTAINS(PROPERTY(file_stream,'Author'),'terry')
67:
68: --搜尋標題屬性為【我的Excel文件】的檔案。
69: SELECT* FROM MyFileTable
70: WHERE CONTAINS(PROPERTY(file_stream,'Title'),'我的Excel文件')
71:
72: --搜尋關鍵字為【pptx】的檔案。
73: SELECT* FROM MyFileTable
74: WHERE CONTAINS(PROPERTY(file_stream,'Tags'),'pptx')
【參考資料】
- sp_fulltext_service (Transact-SQL)
- SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environmen
- SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table
- SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties
- CREATE SEARCH PROPERTY LIST (Transact-SQL)
- ALTER SEARCH PROPERTY LIST (Transact-SQL)
- 尋找搜尋屬性的屬性集 GUID 與屬性整數識別碼
- CONTAINS (Transact-SQL)