Hitcount on Full text search

Question

Hi, we have a full text search query and we need the number of hits (if any). We use contains and FORMSOF INFLECTIONAL in order to get all the forms of a term. Is there a way to get it somehow? Our query is using fulltext search not only with words but with phrases also (i.e. 'email address').

Since ranking is using hitcounts in it's formula we thought it may be available but seems it's not....

Answer 1

The number of rows is returned of course and the rank if using the *TABLE functions, but not the number of hits that happened inside the row.  Sorry.  There are a couple of functions you can use to deduce more, but not everything.  And the functions will run slowly on a large corpus of text.

sys.dm_fts_index_keywords_by_document

Returns information about the document-level content of a full-text index for the specified table. A given keyword can appear in several documents.

sys.dm_fts_index_keywords

Returns information about the content of a full-text index for the specified table.

sys.dm_fts_parser

Returns the final tokenization result after applying a given word breaker, thesaurus, and stoplist combination to a query string input. The output is equivalent to the output if the specified given query string were issued to the Full-Text Engine.

It seems that you are using CONTAINSTABLE to get your results, so Rank is available to you, but not the hit count, as already mentioned  By the way, as of SQL Server 2008 R2 (for sure) FREETEXT and FREETEXTTABLE do not support phrases.
https://connect.microsoft.com/SQLServer/feedback/details/683573/freetext-in-sql-server-2008-r2-no-longer-works-as-documented-for-phrase-searches

If you are curious how things are ranked, there is an explanation on the SQL Server documents:

How Search Query Results Are Ranked (Full-Text Search)

Describes how full-text search generates the rank values returned by a query, including commonly used terms and statistical values that are important in calculating rank, rank computation issues, and the ranking of CONTAINSTABLE and FREETEXTTABLE functions results.

All the best,
RLF