New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2 and SQL Server 2014 SP1

Applies to: SQL Server 2012 EnterpriseSQL Server 2012 DeveloperSQL Server 2012 Standard

Symptoms


In Microsoft SQL Server 2012 Service Pack 2 (SP2) and SQL Server 2014 SP1, a new Dynamic Management Function (DMF) sys.dm_fts_index_keywords_position_by_document is introduced to expose the keyword positional information in the indexed documents. The new DMF specification is provided as follows:
Select * from sys.dm_fts_index_keywords_position_by_document
(
DB_ID('database_name'), OBJECT_ID('table_name')
)
Note You can add a predicate on the document_id as in the following example query, and SQL Server will execute the query in an efficient way.
Select * from sys.dm_fts_index_keywords_position_by_document
(
DB_ID('database_name'), OBJECT_ID('table_name')
)
where document_id = 'id'


The DMF outputs the indexed keywords for the particular document together with the position information. The returned table resembles the following:
ColumnData TypeDefinition
keywordvarbinary(128)indexed keyword
display_termnvarchar(8000)internal fulltext representation
column_idIntcolumn id
document_idBigintdocument id
position Bigintpositional information


Resolution


Service pack information for SQL Server 2014

To resolve this issue, obtain Service Pack 1 for SQL Server 2014.

For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1 .

Service pack information for SQL Server 2012

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.