In the past I’ve talked about some advanced text searching techniques for SQL Server Full Text Searching. Another you can take advantage of is FormsOf. Let’s say we’ve setup a full text search index on the Adventure Works ProductDescription tables’ Description field. (For more info on how to do this, go to the Arcane Lessons page of this site and scroll down to the “Getting Started With SQL Server 2005 Full Text Searching” section.)
FormsOf has two ways to use it, the Inflectional mode and the Thesaurus mode. Let’s look at an example, and then I’ll explain the differences. For this example, we’ll combine data from several tables to get back some meaningful information from the AdventureWorks database.
— Example 1 – FORMSOF INFLECTION
select [Name], ProductNumber, [Description]
from [Production].[Product] p
, [Production].[ProductDescription] pd
, [Production].[ProductModelProductDescriptionCulture] pmpdc
where p.ProductModelID = pmpdc.ProductModelID
and pmpdc.ProductDescriptionID = pd.ProductDescriptionID
and CONTAINS(pd.[Description], ‘FORMSOF(Inflectional, light)’ )
— Example 2 – FORMSOF THESAURUS
select [Name], ProductNumber, [Description]
from [Production].[Product] p
, [Production].[ProductDescription] pd
, [Production].[ProductModelProductDescriptionCulture] pmpdc
where p.ProductModelID = pmpdc.ProductModelID
and pmpdc.ProductDescriptionID = pd.ProductDescriptionID
and CONTAINS(pd.[Description], ‘FORMSOF(Thesaurus, light)’ )
Both examples use the same, I admit, rather bizarre syntax. Inside the string you pass to the CONTAINS clause, you put FORMSOF, with the word Inflectional or Thesaurus, a comma, then the word or phrase you want to search form. In this example I use Contains, but FormsOf also works with FreeText as well.
So what is the difference between Inflectional and Thesaurus? Inflectional finds all of the tenses of a word. For example, if you passed in Start, Inflectional will find Start, Started, and Starting. For nouns, Inflectional finds the single, plural, and possessive forms.
Thesaurus works like you would expect a thesaurus to. It will find variations of a word, Start, Begin, etc. Essentially, words that have the same meaning.
How do these relate to Contains and FreeText? Normally Contains looks for an exact match. FullText matches the meaning, but not the exact words in the query. FullText is very similar to Thesaurus, and even uses the Thesaurus in its work. FullText will break out the search string into its individual words, if there is more than one word. For each word, it generates the inflectional forms of the word, then identifies a list of matches for each word based on the thesaurus. FormsOf(Thesaurus… on the other hand just uses the thesaurus to do the search, without going through the inflectional step.
Using a combination of Contains, FreeText, and FormsOf you can give your users some real flexibility, ranging from exact matches to wide open searches.
Arcane Code 
What about finding plurals, but not other inflections. Does something like FORMSOF(PLURAL, light) exist? (I want to match “dog” with “dogs”, but not “run” with “ran”.
Human – Sorry, nothing like that in FTS. Only suggestion I have is to use OR, and append an S to the string you are passing in. True, for some tenses it fail (goose vs geese) but would catch most of what you need.
That is an excellent summary of the difference between CONTAINS and FREETEXT; I have looked all over the MSDN site for one and not found it, so thank you!
P.S. Mild typo in a few places in para 4 (below the code sample) — “fulltext” for (presumably) “freetext”.
Note to HumanJHawkins: I had exactly this question myself, and used the suggested solution but appending “es” if the word ends in an “s” and a plain “s” otherwise. Obviously, sometimes this will generate nonsense words (e.g. “studieses”) but these won’t be contained in your document and hence won’t produce unwanted hits.
Great summary!! Clear and Concise. The last paragraph where the differences between FreeText and Contains are outlined, FreeText is mentioned as FullText. Can you make the correction so that readers won’t be misguided. Thank You!!