sys.dm_fts_index_keywords_by_document (Transact-SQL)

Artikel
02/28/2023

Applies to: SQL Server Azure SQL Managed Instance Analytics Platform System (PDW)

Returns information about the document-level content of a full-text index associated with the specified table.

sys.dm_fts_index_keywords_by_document is a dynamic management function.

To view higher-level full-text index information

sys.dm_fts_index_keywords (Transact-SQL)

To view information about property-level content related to a document property

sys.dm_fts_index_keywords_by_property (Transact-SQL)

Syntax

  
sys.dm_fts_index_keywords_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_by_document uses to find the specified database. If database_name is omitted, the current database ID is returned.

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	nvarchar(4000)	The hexadecimal representation of the keyword that is stored inside the full-text index. Note: OxFF represents the special character that indicates the end of a file or dataset.
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. Note: OxFF represents the special character that indicates the end of a file or dataset.
column_id	int	ID of the column from which the current keyword was full-text indexed.
document_id	int	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.
occurrence_count	int	Number of occurrences of the current keyword in the document or row that is indicated by document_id. When 'search_property_name' is specified, occurrence_count displays only the number of occurrences of the current keyword in the specified search property within the document or row.

Remarks

The information returned by sys.dm_fts_index_keywords_by_document is useful for finding out the following, among other things:

The total number of keywords that a full-text index contains.
Whether a keyword is part of a given document or row.
How many times a keyword appears in the whole full-text index; that is:

(SUM(occurrence_count) WHERE keyword=keyword_value )
How many times a keyword appears in a given document or row.
How many keywords a given document or row contains.

Also, you can also use the information provided by sys.dm_fts_index_keywords_by_document to retrieve all the keywords belonging to a given document or row.

When the full-text key column is an integer data type, as recommended, the document_id maps directly to the full-text key value in the base table.

In contrast, when the full-text key column uses a non-integer data type, document_id does not represent the full-text key in the base table. In this case, to identify the row in the base table that is returned by dm_fts_index_keywords_by_document, you need to join this view with the results returned by sp_fulltext_keymappings. Before you can join them, you must store the output of the stored procedure in a temp table. Then you can join the document_id column of dm_fts_index_keywords_by_document with the DocId column that is returned by this stored procedure. Note that a timestamp column cannot receive values at insert time, because they are auto-generated by SQL Server. Therefore, the timestamp column must be converted to varbinary(8) columns. The following example shows these steps. In this example, table_id is the ID of your table, database_name is the name of your database, and table_name is the name of your table.

USE database_name;  
GO  
CREATE TABLE #MyTempTable   
   (  
      docid INT PRIMARY KEY ,  
      [key] INT NOT NULL  
   );  
DECLARE @db_id int = db_id(N'database_name');  
DECLARE @table_id int = OBJECT_ID(N'table_name');  
INSERT INTO #MyTempTable EXEC sp_fulltext_keymappings @table_id;  
SELECT * FROM sys.dm_fts_index_keywords_by_document   
   ( @db_id, @table_id ) kbd  
   INNER JOIN #MyTempTable tt ON tt.[docid]=kbd.document_id;  
GO

Permissions

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

Examples

A. Displaying full-text index content at the document level

The following example displays the content of the full-text index at the document level in the HumanResources.JobCandidate table of the AdventureWorks2022 sample database.

Note

You can create this index by executing the example provided for the HumanResources.JobCandidate table in CREATE FULLTEXT INDEX (Transact-SQL).

SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2022'),   
object_id('HumanResources.JobCandidate'));  
GO

Share via