CREATE SEARCH PROPERTY LIST (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
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.
Applies to: SQL Server (SQL Server 2012 through current version).
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.
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.
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.
The owner can be changed by using the ALTER AUTHORIZATION Transact-SQL statement.
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
Requires CREATE FULLTEXT CATALOG permissions in the current database and REFERENCES permissions on any database from which you copy a source property list.
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.
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.
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 AdventureWorks2012; ALTER FULLTEXT INDEX ON Production.Document SET SEARCH PROPERTY LIST DocumentPropertyList WITH NO POPULATION; GO
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 AdventureWorks2012 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 AdventureWorks2012 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 AdventureWorks2012.DocumentPropertyList; GO ALTER FULLTEXT INDEX ON HumanResources.JobCandidate SET SEARCH PROPERTY LIST JobCandidateProperties; GO
ALTER SEARCH PROPERTY LIST (Transact-SQL)
DROP SEARCH PROPERTY LIST (Transact-SQL)
Search Document Properties with Search Property Lists
Find Property Set GUIDs and Property Integer IDs for Search Properties