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:
|
Column |
Data Type |
Definition |
|---|---|---|
|
keyword |
varbinary(128) |
indexed keyword |
|
display_term |
nvarchar(8000) |
internal fulltext representation |
|
column_id |
Int |
column id |
|
document_id |
Bigint |
document id |
|
position |
Bigint |
positional 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.
