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.
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 )