ALTER SEARCH PROPERTY LIST (Transact-SQL)

  • Article

Adds a specified search property to, or drops it from the specified search property list.

Important

CREATE SEARCH PROPERTY LIST, ALTER SEARCH PROPERTY LIST, and DROP SEARCH PROPERTY LIST are supported only under compatibility level 110. Under lower compatibility levels, these statements are not supported.

Syntax

ALTER SEARCH PROPERTY LIST list_name
{
   ADD 'property_name'
     WITH 
      ( 
          PROPERTY_SET_GUID = 'property_set_guid'
        , PROPERTY_INT_ID = property_int_id
      [ , PROPERTY_DESCRIPTION = 'property_description' ]
      )
 | DROP 'property_name' 
}
;

Arguments

  • list_name
    Is the name of the property list being altered. list_name is an identifier.

    To view the names of the existing property lists, use the sys.registered_search_property_lists catalog view, as follows:

    SELECT name FROM sys.registered_search_property_lists;

  • ADD
    Adds a specified search property to the property list specified by list_name. The property is registered for the search property list . Before newly added properties can be used for property searching, the associated full-text index or indexes must be repopulated. For more information, see ALTER FULLTEXT INDEX (Transact-SQL).

    Note

    To add a given search property to a search property list, you must provide its property-set GUID (property_set_guid) and property int ID (property_int_id). For more information, see "Obtaining Property Set GUIDS and Identifiers," later in this topic.

  • property_name
    Specifies the name to be used to identify the property in full-text queries. property_name must uniquely identify the property within the property set. A property name can contain internal spaces. The maximum length of property_name is 256 characters. This name can be a user-friendly name, such as Author or Home Address, or it can be the Windows canonical name of the property, such as System.Author or System.Contact.HomeAddress.

    Developers will need to use the value you specify for property_name to identify the property in the CONTAINS predicate. Therefore, when adding a property it is important to specify a value that meaningfully represents the property defined by the specified property set GUID (property_set_guid) and property identifier (property_int_id). For more information about property names, see "Remarks," later in this topic.

    To view the names of properties that currently exist in a search property list of the current database, use the sys.registered_search_properties catalog view, as follows:

    SELECT property_name FROM sys.registered_search_properties;

  • PROPERTY_SET_GUID ='property_set_guid'
    Specifies the identifier of the property set to which the property belongs. This is a globally unique identifier (GUID). For information about obtaining this value, see "Remarks," later in this topic.

    To view the property set GUID of any property that exists in a search property list of the current database, use the sys.registered_search_properties catalog view, as follows:

    SELECT property_set_guid FROM sys.registered_search_properties;

  • PROPERTY_INT_ID =property_int_id
    Specifies the integer that identifies the property within its property set. For information about obtaining this value, see "Remarks."

    To view the integer identifier of any property that exists in a search property list of the current database, use the sys.registered_search_properties catalog view, as follows:

    SELECT property_int_id FROM sys.registered_search_properties;

    Note

    A given combination of property_set_guid and property_int_id must be unique in a search property list. If you try to add an existing combination, the ALTER SEARCH PROPERTY LIST operation fails and issues an error. This means that you can define only one name for a given property.

  • PROPERTY_DESCRIPTION ='property_description'
    Specifies a user-defined description of the property. property_description is a string of up to 512 characters. This option is optional.

  • DROP
    Drops the specified property from the property list specified by list_name. Dropping a property unregisters it, so it is no longer searchable.

Remarks

Each full-text index can have only one search property list.

To enable querying on a given search property, you must add it to the search property list of the full-text index and then repopulate the index.

When specifying a property you can arrange the PROPERTY_SET_GUID, PROPERTY_INT_ID, and PROPERTY_DESCRIPTION clauses in any order, as a comma-separated list within parentheses, for example:

ALTER SEARCH PROPERTY LIST CVitaProperties
ADD 'System.Author' 
WITH ( 
      PROPERTY_DESCRIPTION = 'Author or authors of a given document.',
      PROPERTY_SET_GUID   = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', 
      PROPERTY_INT_ID = 4 
      );

Note

This example uses the property name, System.Author, which is similar to the concept of canonical property names introduced in Windows Vista (Windows canonical name).

Obtaining Property Values

Full-text search maps a search property to a full-text index by using its property set GUID and property integer ID. For information about how to obtain these for properties that have been defined by Microsoft, see Find Property Set GUIDs and Property Integer IDs for Search Properties. For information about properties defined by an independent software vendor (ISV), see the documentation of that vendor.

Making Added Properties Searchable

Adding a search property to a search property list registers the property. A newly added property can be immediately specified in CONTAINS queries. However, property-scoped full-text queries on a newly added property will not return documents until the associated full-text index is repopulated. For example, the following property-scoped query on a newly added property, new_search_property, will not return any documents until the full-text index associated with the target table (table_name) is repopulated:

SELECT column_name FROM table_name WHERE CONTAINS( PROPERTY( column_name, 'new_search_property' ), 'contains_search_condition');
GO

To start a full population, use the following ALTER FULLTEXT INDEX (Transact-SQL) statement:

USE database_name;
GO
ALTER FULLTEXT INDEX ON table_name START FULL POPULATION;
GO

Note

Repopulation is not needed after a property is dropped from a property list, because only the properties that remain in the search property list are available for full-text querying.

To create a property list

To drop a property list

To add or remove a property list on a full-text index

To run a population on a full-text index

Permissions

Requires CONTROL permission on the property list.

Examples

A. Adding a property

The following example adds several properties—Title, Author, and Tags—to a property list named DocumentPropertyList.

Note

For an example that creates DocumentPropertyList property list, see CREATE SEARCH PROPERTY LIST (Transact-SQL).

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

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

ALTER SEARCH PROPERTY LIST DocumentPropertyList 
    ADD 'Tags'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5, 
      PROPERTY_DESCRIPTION = 'System.Keywords - Set of keywords (also known as tags) assigned to the item.' );

Note

You must associate a given search property list with a full-text index before using it for property-scoped queries. To do so, use an ALTER FULLTEXT INDEX statement and specify the SET SEARCH PROPERTY LIST clause.

B. Dropping a property

The following example drops the Comments property from the DocumentPropertyList property list.

ALTER SEARCH PROPERTY LIST DocumentPropertyList
DROP 'Comments' ;

See Also

Reference

CREATE SEARCH PROPERTY LIST (Transact-SQL)

DROP SEARCH PROPERTY LIST (Transact-SQL)

sys.registered_search_properties (Transact-SQL)

sys.registered_search_property_lists (Transact-SQL)

sys.dm_fts_index_keywords_by_property (Transact-SQL)

Concepts

Search Document Properties with Search Property Lists

Find Property Set GUIDs and Property Integer IDs for Search Properties