Confused whether to use FREETEXTTABLE or CONTAINSTABLE

Question

So I'm using SQL Full Text Search and from what I read, CONTAINS returns matches near words, while FREETEXT returns words that have similar meaning, however I can't find any examples that validate this.

Anyway, to the point, I am using Full Text Search on a table with a description field that is max 2500 chars long and want to use FTS on this. This description will contain things like ingredients for food, what would be the best to use, CONTAINS or FREETEXT? What I want my user to search is somthing like:

"recipe for cake sugar"

and it should return the top ranked results, what would be best fo rthis, FREETEXT or CONTAINs?

Answer 1

I think FREETEXT is what you're looking for.

Here it is a good comparison of the two options:

http://www.sitepoint.com/blogs/2006/12/06/sql-server-full-text-search-protips-part-2-contains-vs-freetext/

If you use CONTAINS, the WHERE turns to be complex, so you can't pass directly what your users have entered in your search form:

WHERE CONTAINS(notes, 'FORMSOF(INFLECTIONAL, recipe) or FORMSOF(INFLECTIONAL, cuisine)') 

But, if you use FREETEXT, you directly specify the "search form" in the where:

WHERE FREETEXT(notes, 'recipe for cake sugar') 

I would say CONTAINS is useful if you want to search something where your program constructs the query because it is far more powerful and you have more control. On the other side, FREETEXT is useful when you want to do a query "google style" that is your user specifies in a simple syntax (just the words) what to search for.