使用搜索属性列表搜索文档属性Search Document Properties with Search Property Lists

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

文档属性的内容先前无法与文档正文的内容区分。The content of document properties was previously indistinguishable from the content of the document body. 此局限性将全文查询限制为针对整个文档进行一般搜索。This limitation restricted full-text queries to generic searches on whole documents. 但现在,对于 varbinaryvarbinary(max) (包括 FILESTREAM)或 image 二进制数据列中支持的文档类型,你可以配置全文索引以支持对特定属性(如 Author 和 Title)进行属性范围内的搜索。Now, however, you can configure a full-text index to support property-scoped searching on particular properties, such as Author and Title, for supported document types in a varbinary, varbinary(max) (including FILESTREAM), or image binary data column. 这种形式的搜索称为“属性搜索” 。This form of searching is known as property searching.

关联的筛选器 (IFilter) 确定能否针对指定的文档类型进行属性搜索。The associated filter (IFilter) determines whether property searching is possible on a specific type of document. 对于某些文档类型,关联的 IFilter 提取为该类型文档定义的某些或所有属性,以及文档正文的内容。For some document types, the associated IFilter extracts some or all of the properties defined for that type of document, as well as the content of the document body. 您可以对全文索引进行配置,以便仅对全文索引期间 IFilter 提取的属性支持属性搜索。You can configure a full-text index to support property searching only on properties that are extracted by an IFilter during full-text indexing. 在提取若干文档属性的 IFilter 中,包括用于提取 Microsoft Office 文档类型(如 .docx、.xlsx 和.pptx)的 IFilter。Among IFilters that extract a number of document properties are the IFilters for Microsoft Office document types (such as .docx, .xlsx, and .pptx). 另一方面,XML IFilter 不发出属性。On the other hand, the XML IFilter does not emit properties.

全文搜索如何与搜索属性一起使用How Full-Text Search Works with Search Properties

内部属性 IDInternal Property IDs

全文引擎任意向每个注册的属性分配一个内部属性 ID,这个 ID 在该特定搜索列表中唯一标识属性并且特定于该搜索属性列表。The Full-Text Engine arbitrarily assigns each registered property an internal property ID, which uniquely identifies the property in that particular search list and which is specific to that search property list. 因此,如果某个属性添加到多个搜索属性列表中,则其内部属性 ID 很可能在不同列表之间是不同的。Therefore, if a property is added to multiple search property lists, its internal property ID is likely to differ between different lists.

在向某个搜索列表注册某一属性时,全文引擎向该属性任意分配一个内部属性 ID 。When a property is registered for a search list, the Full-Text Engine arbitrarily assigns an internal property ID to the property. 该内部属性 ID 是在该搜索属性列表中唯一标识该属性的整数。The internal property ID is an integer that uniquely identifies the property in that search property list.

下图显示一个搜索属性列表的逻辑视图,该搜索属性列表指定两个属性:Title 和 Keywords。The following illustration shows a logical view of a search property list that specifies two properties, Title and Keywords. Keywords 的属性列表名称是“Tags”。The property-list name for Keywords is "Tags." 这些属性属于其 GUID 为 F29F85E0-4FF9-1068-AB91-08002B27B3D9 的相同属性集。These properties belong to the same property set, whose GUID is F29F85E0-4FF9-1068-AB91-08002B27B3D9. 属性整数标识符对于 Title 为 2,对于 Tags (Keywords) 为 5。The property integer identifiers are 2 for Title and 5 for Tags (Keywords). 全文引擎任意将每个属性映射到在搜索属性列表中唯一的内部属性 ID。The Full-Text Engine arbitrarily maps each property to an internal property ID that is unique to the search property list. Title 属性的内部属性 ID 为 1,Tags 属性的内部属性 ID 为 2。The internal property ID for the Title property is 1, and the internal property ID for the Tags property is 2.

搜索属性列表到内部表的映射Mapping of search property list to internal table

内部属性 ID 很可能不同于该属性的属性整数标识符。The internal property ID is likely to be different from the property integer identifier of the property. 如果为多个搜索属性列表注册给定属性,则可能会为每个搜索属性列表指定不同的内部属性 ID。If a given property is registered for multiple search property lists, a different internal property ID might be assigned for each search property list. 例如,内部属性 ID 在一个搜索属性列表中可以是 4,在另一个列表中可以是 1,在其他列表中可以是 3,依此类推。For example, the internal property ID might be 4 in one search property list, 1 in another, 3 in another, and so on. 相反,属性整数标识符是属性所有固有的,并且无论在哪里使用该属性其属性标识符都保持相同。In contrast, the property integer identifier is intrinsic to the property, and it remains the same wherever the property is used.

已注册属性的索引Indexing of Registered Properties

在某个全文索引与搜索属性列表相关联后,必须重新填充该索引以便对特定于属性的搜索词建立索引。After a full-text index is associated with a search property list, the index must be repopulated to index property-specific search terms. 在全文索引期间,所有属性的内容都与其他内容一起存储于全文索引中。During full-text indexing, the contents of all properties are stored in the full-text index along with other content. 但是,在对在某个已注册属性中找到的搜索词建立索引时,全文索引器还将相应的内部属性 ID 与该搜索词一起存储。However, when indexing a search term found in a registered property, the full-text indexer also stores the corresponding internal property ID with the term. 相反,如果未注册某个属性,则该属性将存储于全文索引中,就像它是文档正文的一部分,并且对于内部属性 ID,该属性的值为零。In contrast, if a property is not registered, it is stored in the full-text index as if it were part of the document body, and it has a value of zero for the internal property ID.

下图显示一个逻辑视图,说明搜索词如何出现在与上图中所示的搜索属性列表相关联的全文索引中。The following illustration shows a logical view of how search terms appear in a full-text index that is associated with the search property list shown in the preceding illustration. 示例文档 Document 1 包含三个属性(Title、Author 和 Keywords)以及文档正文。A sample document, Document 1 contains three properties-Title, Author, and Keywords-as well as the document body. 对于在搜索属性列表中指定的属性 Title 和 Keywords,搜索词与其在全文索引中的相应内部属性 ID 相关联。For the properties Title and Keywords, which are specified in the search property list, search terms are associated with their corresponding internal property IDs in the full-text index. 相反,将对 Author 属性的内容建立索引,就像它是文档正文的一部分。In contrast, the content of the Author property is indexed as if it were part of the document body. 这意味着,注册某一属性将在一定程度上增加全文索引的大小,具体取决于属性中存储的内容量。This means registering a property increases the size of the full-text index somewhat, depending on the amount of content stored in the property.

使用搜索属性列表的全文检索Full-text index that uses a search property list

Title 属性中的搜索词(“Favorite”、“Biking”和“Trails”)与分配给此索引的 Title 的内部属性 ID (1) 相关联。Search terms in the Title property-"Favorite," "Biking," and "Trails"-are associated with the internal property ID assigned to Title for this index, 1. Keywords 属性中的搜索词(“biking”和“mountain”)与分配给此索引的 Tags 的内部属性 ID (2) 相关联。Search terms in the Keywords property-"biking" and "mountain"-are associated with the internal property ID assigned to Tags for this index, 2. 对于 Author 属性(“Jane”和“Doe”)的搜索词 n 以及文档正文中的搜索词,该内部属性 ID 为 0。For search terms n the Author property-"Jane" and "Doe"-and search terms in the document body, the internal property ID is 0. “biking”一词在 Title 属性、Keywords (Tags) 属性以及文档正文中出现。The term "biking" occurs in the Title property, in the Keywords (Tags) property, and in the document body. 针对 Title 或 Keywords (Tags) 属性中“biking”的属性搜索将在结果中返回此文档。A property search for "biking" in the Title or Keywords (Tags) property would return this document in the results. 针对“biking”的一般全文查询也返回此文档,就像没有为属性搜索配置索引。A generic full-text query for "biking" would also return this document, as if the index were not configured for property searching. 在 Author 属性中针对“biking”的属性搜索将不返回此文档。A property search for "biking" in the Author property would not return this document.

属性范围的全文查询使用向全文索引的当前搜索属性列表注册的内部属性 ID。A property-scoped full-text query uses the internal property IDs registered for the current search property list of the full-text index.

启用属性搜索的影响Impact of Enabling Property Searching

根据您在搜索属性列表中指定的属性的数目以及每个属性的内容,配置全文索引以便支持搜索一个或多个属性将在某种程度上增加索引的大小。Configuring a full-text index to support searching on one or more properties increases the size of the index somewhat, depending on the number of properties you specify in your search property list and on the content of each property.

在测试 Microsoft Word、Excel 和 PowerPoint 文档的典型资料时,我们配置了一个全文索引以便对典型的搜索属性建立索引。In testing typical corpuses of Microsoft Word, Excel, and PowerPoint documents, we configured a full-text index to index typical search properties. 对这些属性建立索引将全文索引的大小增加了大约 5%。Indexing these properties increased the size of the full-text index size by approximately 5 percent. 我们预计这一大小上的增量对于大多数文档资料而言大致相近。We anticipate that this approximate size increase will to be typical for most document corpuses. 不过,这个大小上的增量最终将取决于相对于整个数据量,给定文档资料中的属性数据量的比例。However, ultimately, the size increase will depend on the amount of property data in a given document corpus relative to the amount of overall data.

创建搜索属性列表并启用属性搜索Creating a Search Property List and Enabling Property Search

创建搜索属性列表Creating a Search Property List

使用 Transact-SQL 创建搜索属性列表To create a search property list with Transact-SQL

使用 CREATE SEARCH PROPERTY LIST (Transact-SQL) 语句并至少提供该列表中的一个名称。Use the CREATE SEARCH PROPERTY LIST (Transact-SQL) statement and provide at least a name the list.

在 Management Studio 中创建搜索属性列表To create a search property list in Management Studio
  1. 在对象资源管理器中,展开服务器。In Object Explorer, expand the server.

  2. 展开 “数据库” ,然后展开要在其中创建搜索属性列表的数据库。Expand Databases, and then expand the database in which you want to create the search property list.

  3. 展开“存储” ,然后右键单击“搜索属性列表” 。Expand Storage, and then right-click Search Property Lists.

  4. 选择 “新建搜索属性列表”Select New Search Property List.

  5. 指定该属性列表的名称。Specify the property list name.

  6. 还可以选择将其他人指定为该属性列表的所有者。Optionally, specify someone else as the property list owner.

  7. 选择以下选项之一:Select one of the following options:

    • 创建空的搜索属性列表Create an empty search property list

    • 从现有搜索属性列表创建Create from an existing search property list

    有关详细信息,请参阅 New Search Property ListFor more information, see New Search Property List.

  8. 单击“确定”。 Click OK.

将属性添加到搜索属性列表Adding Properties to a Search Property List

属性搜索要求创建“搜索属性列表” 并且指定您希望可供搜索的一个或多个属性。Property searching requires creating a search property list and specifying one or more properties that you want to make searchable. 在您向搜索属性列表添加某一属性时,将向该特定列表注册该属性。When you add a property to a search property list, the property is registered for that particular list. 若要向搜索属性列表添加属性,您需要以下值:To add a property to a search property list you need the following values:

  • 属性集 GUIDProperty set GUID

    每个搜索属性都属于包含一组相关属性的单个属性集。Each search property belongs to single property set that contains a group of related properties. 每个属性集均由全局唯一标识符 (GUID) 标识。Each property set is identified by a globally unique identifier (GUID).

  • 属性整数标识符Property integer identifier

    每个搜索属性都拥有在属性集内唯一的标识符。Each search property possesses an identifier that is unique within the property set. 对于某一给定属性,该标识符可以是整数或字符串,但全文搜索仅支持整数标识符。For a given property, the identifier could be either an integer or a string, however full-text search supports only integer identifiers.

  • 属性名称Property name

    该名称是用户将在全文查询中为搜索属性指定的名称。This is the name that users will specify in full-text queries to search on the property. 属性名称可以包含内部空格。A property name can contain internal spaces. 最大长度为 256 个字符。The maximum length is 256 characters.

    属性名称可以是以下任何项:The property name can be any of the following:

    • 属性的 Windows 规范名称,例如 System.AuthorSystem.Contact.HomeAddressThe Windows canonical name of the property, such as System.Author or System.Contact.HomeAddress.

    • 便于您的用户记住的用户友好名称。A user-friendly name that is easy for your users to remember. 某些属性与已知的用户友好名称(例如“Author”或“Home Address”)相关联,但您可以指定最适合您的用户的任何名称。Some properties are associated with a well-known user-friendly name, such as "Author" or "Home Address," but you can specify whatever name is most appropriate to your users.

    备注

    属性集 GUID 和属性标识符的给定组合在给定搜索属性列表内必须唯一。A given combination of property set GUID and property identifier must be unique in a given search property list. 这意味着,您不能使用不同的名称或说明多次添加同一属性。This means that you cannot add the same property more than once with different names or descriptions.

  • 属性说明(可选)Property description (optional)

    在您将某个搜索属性添加到搜索属性列表时,您可以提供可选说明。When adding a search property to a search property list, you can supply an optional description. 例如,您可能要提供与其名称未表露其含义的属性有关的信息,或者您可能想要描述该属性的属性集。For example, you might want to provide information about a property that is not evident from its name, or you might want to describe the property set of the property.

获取搜索属性列表的值To obtain values for a search property list

请参阅 查找搜索属性的属性集 GUID 和属性整数 IDSee Find Property Set GUIDs and Property Integer IDs for Search Properties.

使用 Transact-SQL 将属性添加到搜索属性列表中To add a property to a search property list with Transact-SQL

通过借助查找搜索属性的属性集 GUID 和属性整数 ID 一文中介绍的方法之一获得的值使用 ALTER SEARCH PROPERTY LIST (Transact-SQL) 语句。Use the ALTER SEARCH PROPERTY LIST (Transact-SQL) statement with the values that you obtained by using one of the methods described in the article, Find Property Set GUIDs and Property Integer IDs for Search Properties.

下面的示例演示在将属性添加到搜索属性列表时这些值的用法:The following example demonstrates the use of these values when adding a property to a search property list:

ALTER SEARCH PROPERTY LIST DocumentTablePropertyList  
   ADD 'Title'  
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,   
      PROPERTY_DESCRIPTION = 'System.Title - Title of the item.' );  

将属性添加到 Management Studio 中的搜索属性列表To add a property to a search property list in Management Studio

使用 “搜索属性列表属性” 对话框以添加或删除搜索属性。Use the Search Property List Properties dialog box to add and remove search properties. 在对象资源管理器中,您可以在关联数据库的 “存储” 节点下找到 “搜索属性列表”You can find Search Property Lists in Object Explorer under the Storage node of the associated database.

将搜索属性列表与全文索引关联Associating a Search Property List with a Full-Text Index

为使全文索引支持对向搜索属性列表注册的属性执行属性搜索,您需要将搜索属性列表与索引相关联并且重新填充该索引。For a full-text index to support property searching on the properties that are registered for a search property list, you need to associate the search property list with the index and repopulate the index. 重新填充全文索引将为每个已注册属性中的搜索词创建特定于属性的索引条目。Repopulating the full-text index creates property-specific index entries for search terms in each of the registered properties.

只要全文索引保持与此搜索属性列表相关联,全文查询就可以使用 CONTAINS 谓词的 PROPERTY 选项来搜索为该搜索属性列表注册的属性。As long as the full-text index remains associated with this search property list, full-text query can use the PROPERTY option of the CONTAINS predicate to search on properties that are registered for that search property list.

如果您更改与全文索引关联的搜索属性列表,则该索引必须重新生成以使其进入一致状态。If you change the search property list associated with a full-text index, then the index must be rebuilt to bring it into a consistent state. 将立即截断索引,并在运行完全填充之前保留为空。The index is truncated immediately and is empty until the full population runs. 有关更改搜索属性列表在何时将导致重新生成索引的详细信息,请参阅 ALTER FULLTEXT INDEX (Transact-SQL) 中的“注释”。For more information about when changing the search property list causes rebuilding the index, see "Remarks," in ALTER FULLTEXT INDEX (Transact-SQL).

使用 Transact-SQL 将搜索属性列表与全文索引相关联To associate a search property list with a full-text index with Transact-SQL

ALTER FULLTEXT INDEX (Transact-SQL) 语句和 SET SEARCH PROPERTY LIST = <property_list_name> 子句结合使用。Use the ALTER FULLTEXT INDEX (Transact-SQL) statement with the SET SEARCH PROPERTY LIST = <property_list_name> clause.

使用 Management Studio 将搜索属性列表与全文索引相关联To associate a search property list with a full-text index with Management Studio

在“全文索引属性” 对话框的“常规” 页上,为“搜索属性列表” 指定一个值。Specify a value for Search Property List on the General page of the Full-Text Index Properties dialog box.

使用 CONTAINS 查询搜索属性Querying Search Properties with CONTAINS

针对属性范围的全文查询的基本 CONTAINS 语法如下:The basic CONTAINS syntax for a property-scoped full-text query is as follows:

SELECT column_name FROM table_name  
  WHERE CONTAINS ( PROPERTY ( column_name, 'property_name' ), '<contains_search_condition>' )  

例如,下面的查询在 Title数据库的 Document 表的 Production.Document 列中搜索索引属性 AdventureWorksFor example, the following query searches on an indexed property, Title, in the Document column of the Production.Document table of the AdventureWorks database. 该查询仅返回其 Title 属性包含字符串 MaintenanceRepairThe query returns only documents whose Title property contains the string Maintenance or Repair

USE AdventureWorks  
GO  
SELECT Document FROM Production.Document  
  WHERE CONTAINS ( PROPERTY ( Document, 'Title' ), 'Maintenance OR Repair')  
GO  

该示例假定文档的 IFilter 提取其 Title 属性,然后将 Title 属性添加到搜索属性列表,并且搜索属性列表与全文索引相关联。This example assumes that the IFilter for the document extracts its Title property, that the Title property is added to the search property list, and that the search property list is associated with the full-text index.

管理搜索属性列表Managing Search Property Lists

查看和更改搜索属性列表Viewing and Changing a Search Property List

使用 Transact-SQL 更改搜索属性列表To change a search property list with Transact-SQL

使用 ALTER SEARCH PROPERTY LIST (Transact-SQL) 语句添加或删除搜索属性。Use the ALTER SEARCH PROPERTY LIST (Transact-SQL) statement to add or remove search properties.

查看和更改 Management Studio 中的搜索属性列表To view and change a search property list in Management Studio
  1. 在对象资源管理器中,展开服务器。In Object Explorer, expand the server.

  2. 展开 “数据库” ,然后展开数据库。Expand Databases, and then expand the database.

  3. 展开 “存储”Expand Storage.

  4. 展开 “搜索属性列表” 以显示搜索属性列表。Expand Search Property Lists to display the search property lists.

  5. 右键单击该属性列表,然后选择“属性” 。Right-click the property list, and select Properties.

  6. “搜索属性列表编辑器” 对话框中,使用“属性”网格添加或删除搜索属性:In the Search Property List Editor dialog box, use the Properties grid to add or remove search properties:

    1. 若要删除某个文档属性,请单击该属性左侧的行标题,然后按 Del。To remove a document property, click the row header to the left of the property, and press DEL.

    2. 若要添加某个文档属性,请在该列表底部的空行中单击,然后在 * 右侧为这个新属性输入值。To add a document property, click in the empty row at the bottom of the list, to the right of the *, and enter the values for the new property.

      有关这些值的信息,请参阅 搜索属性列表编辑器For information about these values, see Search Property List Editor. 有关如何获取由 Microsoft 定义的属性的这些值的信息,请参阅 查找搜索属性的属性集 GUID 和属性整数 IDFor information about how to obtain these values for properties defined by Microsoft, see Find Property Set GUIDs and Property Integer IDs for Search Properties. 有关由独立软件供应商 (ISV) 定义的属性的信息,请参阅该供应商提供的文档。For information about properties defined by an independent software vendor (ISV), see the documentation of that vendor.

  7. 单击“确定”。 Click OK.

删除搜索属性列表Deleting a Search Property List

在属性列表与任何全文索引关联时,不能从数据库中删除该列表。You cannot drop a property list from a database while the list is associated with any full-text index.

使用 Transact-SQL 删除搜索属性列表To delete a search property list with Transact-SQL

使用 DROP SEARCH PROPERTY LIST (Transact-SQL)语句。Use the DROP SEARCH PROPERTY LIST (Transact-SQL) statement.

删除 Management Studio 中的搜索属性列表To delete a search property list in Management Studio
  1. 在对象资源管理器中,展开服务器。In Object Explorer, expand the server.

  2. 展开 “数据库” ,然后展开数据库。Expand Databases, and then expand the database.

  3. 展开 “存储” ,然后展开 “搜索属性列表” 节点。Expand Storage, and then expand the Search Property Lists node.

  4. 右键单击要删除的属性列表,然后单击“删除” 。Right-click the property list that you want to delete, and click Delete.

  5. 单击“确定”。 Click OK.

另请参阅See Also

查找搜索属性的属性集 GUID 和属性整数 ID Find Property Set GUIDs and Property Integer IDs for Search Properties
配置和管理搜索筛选器Configure and Manage Filters for Search