ALTER SEARCH PROPERTY LIST (Transact-SQL)

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:

Copy

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:

Copy

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:

Copy

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:

Copy

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.

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

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:

Copy

            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:

Copy

            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

• CREATE SEARCH PROPERTY LIST (Transact-SQL)

To drop a property list

• DROP SEARCH PROPERTY LIST (Transact-SQL)

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

• ALTER FULLTEXT INDEX (Transact-SQL)

To run a population on a full-text index

• ALTER FULLTEXT INDEX (Transact-SQL)

Requires CONTROL permission on the property list.

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).

Copy

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

ALTER SEARCH PROPERTY LIST DocumentPropertyList
DROP 'Comments' ;

Reference

Concepts