Hit Highlighting/Summarization product for SQL Server Full-Text Search

I've often asked about add-ins for SQL Server Full-Text Search feature. And the most requested feature for FTS has long been some way to accomodate hit highlighting. Recently, I've come across a beta for a product that does just this, along with document summarization formats for display, ThinkHighlight by Interactive Thoughts.

The add-in is implemented as a single SQLCLR assembly that exposes a pair of UDFs, HitHighlightContext and HitHighlight. The first UDF prepares a context. You only have to do this once per query, so this appears to be a nice optimization. The HitHighlight one is used to produce a column of output with summarized text and highlighted hits. You have a choice of three different output summarization strategies: baseline, complete, and top-fragment. Baseline and top-fragment are meant to be used in a manner similar to one that you'd see used in search websites. There's an interactive demo that shows some examples.

The product comes with a nice installer that populates the assembly (its an unsafe assembly as it needs to access COM components like IFilters). However, it doesn't require that user databases be marked trustworth, there's an asymmetric key installed in master, in accordance with best practices.  And I haven't been able to cause any problems with the functions, try as I might. And it works with any type of full-text indexed column (text. binary, as well as filetable) and query (all four FTS predicates/table-valued functions).

If you're looking for extended functionality currently not supported in the FTS feature, this might be right up your alley.

Cheers, Bob

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.