Get word frequencies from SQL Server Full Text Search

Question

SQL Server Full-Text Search computes rank based on the frequency of the word in the document and in the set of all documents (TFIDF). Is it possible to access these values directly ?

I would like to find the top-n most frequent words in my table. Is it possible to obtain this list from the full-text search index?

I'm using SQL Server 2005.

Answer 1

I don't know of an easy way to do this on SQL Server 2005, but on 2008 there are two Transact-SQL commands do exactly what you need.

• sys.dm_fts_index_keywords_by_document
  • http://technet.microsoft.com/en-us/library/cc280607.aspx
• sys.dm_fts_index_keywords
  • http://technet.microsoft.com/en-us/library/cc280900.aspx