sys.dm_fts_index_keywords_position_by_document (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server

Returns keyword positional information in the indexed documents.

Syntax

sys.dm_fts_index_keywords_position_by_document  
(   
    DB_ID('database_name'),   
OBJECT_ID('table_name')   
)

Arguments

db_id('database_name')
A call to the DB_ID() function. This function accepts a database name and returns the database ID, which sys.dm_fts_index_keywords_position_by_document uses to find the specified database.

object_id('table_name')
A call to the OBJECT_ID() function. This function accepts a table name and returns the table ID of the table containing the full-text index to inspect.

Table Returned

Column	Data type	Description
keyword	varbinary(128)	Binary string representing the keyword.
display_term	nvarchar(4000)	The human-readable format of the keyword. This format is derived from the internal format that is stored in the full-text index.
column_id	int	ID of the column from which the current keyword was full-text indexed.
document_id	bigint	ID of the document or row from which the current term was full-text indexed. This ID corresponds to the full-text key value of that document or row.
position	int	The position of the keyword in the document.

Remarks

Use the DMV to identify the location of indexed words in indexed documents. This DMV can be used to troubleshoot issues when sys.dm_fts_index_keywords_by_document indicates the words are in the full-text index, but when you run a query using those words, the document is not returned.

Permissions

Requires SELECT permission on the columns covered by the full-text index and CREATE FULLTEXT CATALOG permissions.

Examples

The following example returns keywords from the full-text index of the Production.Document table of the AdventureWorks sample database.

USE AdventureWorks2022;  
GO   
  
SELECT * FROM sys.dm_fts_index_keywords_position_by_document  
(   
    DB_ID('AdventureWorks2022'),  
    OBJECT_ID('AdventureWorks2022.Production.Document')   
);   
GO

You can add a predicate on the other columns_id as in the following example query, to further isolate the locations.

SELECT * FROM sys.dm_fts_index_keywords_position_by_document  
(   
    DB_ID('AdventureWorks2022'),  
    OBJECT_ID('AdventureWorks2022.Production.Document')   
)  
WHERE document_id = 7 AND display_term = 'performance';