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.

Answer 2

CONTAINSTABLE

http://msdn.microsoft.com/en-us/library/ms189760(v=SQL.90).aspx Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

FREETEXTTABLE

http://msdn.microsoft.com/en-us/library/ms177652(v=SQL.90).aspx Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.