3

My question is similar to this one: How can I know when SQL Full Text Index Population is finished? only instead of population status of the whole index I need to know if SQL Server Full Text Search (FTS) has already indexed certain record in the table.

In other words, given a key from the table is there a way to check if that row has already been picked up by FTS daemon, analyzed and included into corresponding FTS index?

The question is because I'd like to piggyback FTS and use CONTAINS to check if newly added row matches certain conditions.

Community
  • 1
  • 1
Sergey Tarasov
  • 858
  • 11
  • 18

1 Answers1

4

Use sp_fulltext_keymappings. If a record exists in this result set where key = [primary key value from your table] then the table record is indexed.

To get a list of all records that are indexed:

declare @objectId int = OBJECT_ID('dbo.Table1')
exec sp_fulltext_keymappings @objectId

To determine if a single record is indexed, pass the primary key value as the 3rd parameter in the query below. If the result set is empty then the record is not indexed.

declare @objectId int = OBJECT_ID('dbo.Table1')
exec sp_fulltext_keymappings @objectId, null, 1
Keith
  • 20,636
  • 11
  • 84
  • 125
  • Thanks! If you have a real field experience with the procedure, does it have performance implications or it's about the same cost as querying of the table key referenced in full text index? – Sergey Tarasov Mar 11 '15 at 14:44
  • I'm not sure and unfortunately I don't have an instance of MS SQL with full text right now so that I could check. I recommend viewing the source of the stored procedure to see what it is doing. If it is simply selecting from a table then I would bet the performance is good because MS SQL will need to query that same table whenever a full text query is run. – Keith Mar 11 '15 at 16:21
  • `sp_fulltext_keymappings` appears to be an extended procedure (at least in SQL2008R2) so I could not get it's source code. I guess I'll have to try it to see how it performs. Thanks for help! – Sergey Tarasov Mar 12 '15 at 06:09
  • 1
    As per it's actual execution plan invoking the procedure with table_id, NULL, key yields one Clustered Index Seek. – Sergey Tarasov Mar 13 '15 at 03:52