Wednesday, January 23, 2013 11:11 PM
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....
- Moved by Naomi NMicrosoft Community Contributor Wednesday, January 23, 2013 11:46 PM Better answer can be here
Thursday, January 24, 2013 8:24 PM
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.
Returns information about the document-level content of a full-text index for the specified table. A given keyword can appear in several documents.
Returns information about the content of a full-text index for the specified table.
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.
If you are curious how things are ranked, there is an explanation on the SQL Server documents:
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,