sys.dm_fts_index_keywords_position_by_document (Transact-SQL)
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';
See Also
Full-Text Search
Improve the Performance of Full-Text Indexes
Full-Text Search and Semantic Search Functions (Transact-SQL)
Full-Text Search and Semantic Search Dynamic Management Views and Functions (Transact-SQL)
Full-Text Search and Semantic Search Stored Procedures (Transact-SQL)
Search Document Properties with Search Property Lists
sys.dm_fts_index_keywords_by_document (Transact-SQL)
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for