Full Text Indexing Information

Thank you to those of you that came to see my presentation on full text indexes at SQL Saturday #615 in Baltimore!  As promised, please find the slide deck and table builder/ sample queries script at these links.  Also, I have included a few links to useful Microsoft documentation pages below to help you continue to expand your knowledge.

CONTAINS FUNCTION

FREETEXT FUNCTION

FULL TEXT EXAMPLE LIST

A few notes:

I received an explanation for the strange query plan functionality I showed at the end of the class.  It turns out I was 100% wrong.  The plan was showing just fine and will show a key lookup when necessary.  🙂

Also, the logic to determine the rank column of a CONTAINSTABLE search is publicly available.  You can read about it here.  In short, it looks like this.

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount ) Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *