sp_fulltext_keymappings (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Analytics Platform System (PDW)
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.
Transact-SQL syntax conventions
Syntax
sp_fulltext_keymappings { table_id | table_id , docId | table_id , NULL , key }
[ ; ]
Arguments
table_id
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.
docId
An internal document identifier (DocId) that corresponds to the key value. An invalid docId value returns no results.
key
The full-text key value from the specified table. An invalid key value returns no results. For information about full-text key values, see Create and manage full-text indexes.
Return code values
None.
Result set
Column name | Data type | Description |
---|---|---|
DocId |
bigint | An internal document identifier (DocId) column that corresponds to the key value. |
Key |
1 | The full-text key value from the specified table. If no full-text keys exist in the mapping table, an empty rowset is returned. |
1 The data type for Key is same as the data type of the full-text key column in the base table.
Permissions
This function is public and doesn't require any special permissions.
Remarks
The following table describes the effect of using one, two, or three parameters.
This parameter list... | Has this result... |
---|---|
table_id | When 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 various issues. It's useful for seeing the full-text index content when the selected full-text key isn't 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.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 large table for which the performance cost of returning the entire key map might be substantial. |
table_id, docId | If only the table_id and docId are specified, docId must be non-NULL 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 , key |
If three parameters are present, the second parameter must be NULL , and key must be non-NULL 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 isn't full-text indexed
NULL
is encountered for a parameter that might be non-null.
Examples
Note
The examples in this section use the Production.ProductReview
table of the AdventureWorks2022 sample database. You can create this index by executing the example provided for the ProductReview
table in CREATE FULLTEXT INDEX.
A. Obtain all the Key and DocId values
The following example uses a DECLARE @local_variable 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.
Note
Using sp_fulltext_keymappings
with only the table_id parameter is suitable for small tables.
USE AdventureWorks2022;
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:
TABLE | docId | key |
---|---|---|
1 |
1 |
1 |
2 |
2 |
2 |
3 |
3 |
3 |
4 |
4 |
4 |
B. Obtain 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.
Note
Using sp_fulltext_keymappings
with only the table_id parameteris suitable for small tables.
USE AdventureWorks2022;
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.
TABLE | docId | key |
---|---|---|
4 |
4 |
4 |