TechNet
Export (0) Print
Expand All

sp_fulltext_keymappings (Transact-SQL)

 

Returns mappings between document identifiers (DocIds) and full-text key values. The DocId column contains values for a bigint integer that maps to a particular full-text key value in a full-text indexed table. DocId values that satisfy a search condition are passed from the Full-Text Engine to the Database Engine, where they are mapped to full-text key values from the base table being queried. The full-text key column is a unique index that is required on one column of the table.

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12.

Topic link icon Transact-SQL Syntax Conventions

  
sp_fulltext_keymappings { table_id | table_id, docid | table_id, NULL, key }  

Parameters

table_id
Is the object ID of the full-text indexed table. If you specify an invalid table_id, an error is returned. For information about obtaining the object ID of a table, see OBJECT_ID (Transact-SQL).

docid
Is an internal document identifier (DocId) that corresponds to the key value. An invalid docid value returns no results.

key
Is the full-text key value from the specified table. An invalid key value returns no results. For information about full-text key values, see Manage Full-Text Indexes.

System_CAPS_ICON_important.jpg Important


For information about using one, two, or three parameters, see "Remarks," later in this topic.

None.

Column nameData typeDescription
DocIdbigintIs an internal document identifier (DocId) column that corresponds to the key value.
Key*Is the full-text key value from the specified table.

If no full-text keys exist in the mapping table, an empty rowset is returned.

* The data type for Key is same as the data type of the full-text key column in the base table.

This function is public and does not require any special permissions.

The following table describes the effect of using one, two, or three parameters.

This parameter list…Has this result…
table_idWhen invoked with only the table_id parameter, sp_fulltext_keymappings returns all full-text key (Key) values from the specified base table, along with the DocId that corresponds to each key. This includes keys that are pending delete.

This function is useful for troubleshooting a variety of issues. It is particularly useful for seeing the full-text index content when the selected full-text key is not of an integer data type. This involves joining the results of sp_fulltext_keymappings with the results of sys.dm_fts_index_keywords_by_document. For more information, see sys.dm_fts_index_keywords_by_document (Transact-SQL).

In general, however, we recommend that, if possible, you execute sp_fulltext_keymappings with parameters that specify a specific full-text key or DocId. This is much more efficient than returning an entire key map, especially for a very large table for which the performance cost of returning the entire key map might be substantial.
table_id, docidIf only the table_id and docid are specified, docid must be nonNULL and specify a valid DocId in the specified table. This function is useful to isolate the custom full-text key from the base table that corresponds to the DocId of a particular full-text index.
table_id, NULL, keyIf three parameters are present, the second parameter must be NULL, and key must be nonNULL and specify a valid full-text key value from the specified table. This function is useful in isolating the DocId that corresponds to a particular full-text key from the base table.

An error is returned under any of the following conditions:

  • You specify an invalid table_id.

  • The table is not full-text indexed.

  • NULL is encountered for a parameter that may be nonNULL

System_CAPS_ICON_note.jpg Note


The examples in this section use the Production.ProductReview table of the AdventureWorks2012 sample database. You can create this index by executing the example provided for the ProductReview table in CREATE FULLTEXT INDEX (Transact-SQL).

A. Obtaining all the Key and DocId values

The following example uses a DECLARE statement to create a local variable, @table_id and to assign the ID of the ProductReview table as its value. The example executes sp_fulltext_keymappings specifying @table_id for the table_id parameter.

System_CAPS_ICON_note.jpg Note


Using sp_fulltext_keymappings with only the table_id parameter is suitable for small tables.

USE AdventureWorks2012;  
GO  
DECLARE @table_id int = OBJECT_ID(N'Production.ProductReview');  
EXEC sp_fulltext_keymappings @table_id;  
GO  
  

This example returns all the DocIds and full-text keys from the table, as follows:

docidkey
111
222
333
444

B. Obtaining the DocId value for a specific Key value

The following example uses a DECLARE statement to create a local variable, @table_id, and to assign the ID of the ProductReview table as its value. The example executes sp_fulltext_keymappings specifying @table_id for the table_id parameter, NULL for the docid parameter, and 4 for the key parameter.

System_CAPS_ICON_note.jpg Note


Using sp_fulltext_keymappings with only the table_id parameteris suitable for small tables.

USE AdventureWorks2012;  
GO  
DECLARE @table_id int = OBJECT_ID(N'Production.ProductReview');  
EXEC sp_fulltext_keymappings @table_id, NULL, 4;  
GO  
  

This example returns the following results.

docidkey
444

Full-Text Search and Semantic Search Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft