CREATE SEARCH PROPERTY LIST (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Creates a new search property list. A search property list is used to specify one or more search properties that you want to include in a full-text index.

Transact-SQL syntax conventions

Syntax

CREATE SEARCH PROPERTY LIST new_list_name  
   [ FROM [ database_name. ] source_list_name ]  
   [ AUTHORIZATION owner_name ]  
;  

Arguments

new_list_name
Is the name of the new search property list. new_list_name is an identifier with a maximum of 128 characters. new_list_name must be unique among all property lists in the current database, and conform to the rules for identifiers. new_list_name will be used when the full-text index is created.

database_name
Is the name of the database where the property list specified by source_list_name is located. If not specified, database_name defaults to the current database.

database_name must specify the name of an existing database. The login for the current connection must be associated with an existing user ID in the database specified by database_name. You must also have the required permissions on the database.

source_list_name
Specifies that the new property list is created by copying an existing property list from database_name. If source_list_name does not exist, CREATE SEARCH PROPERTY LIST fails with an error. The search properties in source_list_name are inherited by new_list_name.

AUTHORIZATION owner_name
Specifies the name of a user or role to own of the property list. owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. If not specified, ownership is given to the current user.

Note

The owner can be changed by using the ALTER AUTHORIZATIONTransact-SQL statement.

Remarks

Note

For information about property lists in general, see Search Document Properties with Search Property Lists.

By default, a new search property list is empty and you must alter it to manually to add one or more search properties. Alternatively, you can copy an existing search property list. In this case, the new list inherits the search properties of its source, but you can alter the new list to add or remove search properties. Any properties in the search property list at the time of the next full population are included in the full-text index.

A CREATE SEARCH PROPERTY LIST statement fails under any of the following conditions:

• If the database specified by database_name does not exist.

• If the list specified by source_list_name does not exist.

• If you do not have the correct permissions.

To add or remove properties from a list

• ALTER SEARCH PROPERTY LIST (Transact-SQL)

• To drop a property list

• DROP SEARCH PROPERTY LIST (Transact-SQL)

Permissions

Requires CREATE FULLTEXT CATALOG permissions in the current database and REFERENCES permissions on any database from which you copy a source property list.

Note

REFERENCES permission is required to associate the list with a full-text index. CONTROL permission is required to add and remove properties or drop the list. The property list owner can grant REFERENCES or CONTROL permissions on the list. Users with CONTROL permission can also grant REFERENCES permission to other users.

Examples

A. Creating an empty property list and associating it with an index

The following example creates a new search property list named DocumentPropertyList. The example then uses an ALTER FULLTEXT INDEX statement to associate the new property list with the full-text index of the Production.Document table in the AdventureWorks database, without starting a population.

Note

For an example that adds several predefined, well-known search properties to this search property list, see ALTER SEARCH PROPERTY LIST (Transact-SQL). After adding search properties to the list, the database administrator would need to use another ALTER FULLTEXT INDEX statement with the START FULL POPULATION clause.

CREATE SEARCH PROPERTY LIST DocumentPropertyList;  
GO  
USE AdventureWorks2022;  
ALTER FULLTEXT INDEX ON Production.Document   
   SET SEARCH PROPERTY LIST DocumentPropertyList  
   WITH NO POPULATION;   
GO   

B. Creating a property list from an existing one

The following example creates a new the search property list, JobCandidateProperties, from the list created by Example A, DocumentPropertyList, which is associated with a full-text index in the AdventureWorks2022 database. The example then uses an ALTER FULLTEXT INDEX statement to associate the new property list with the full-text index of the HumanResources.JobCandidate table in the AdventureWorks2022 database. This ALTER FULLTEXT INDEX statement starts a full population, which is the default behavior of the SET SEARCH PROPERTY LIST clause.

CREATE SEARCH PROPERTY LIST JobCandidateProperties 
FROM AdventureWorks2022.DocumentPropertyList;  
GO  
ALTER FULLTEXT INDEX ON HumanResources.JobCandidate   
   SET SEARCH PROPERTY LIST JobCandidateProperties;  
GO

See Also

ALTER 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)
Search Document Properties with Search Property Lists
Find Property Set GUIDs and Property Integer IDs for Search Properties