Index Server Faq
A CONTAINS predicate will by default do a character‑by‑character match on your search phrase. It is a very precise query. FREETEXT is by default a fuzzy, liberal, or imprecise query. All of the keywords that apply with CONTAINS also apply, but there are some differences.
The first caveat is when you do a search on a multi‑word search phrase and do not qualify what column name you are using, you may get unpredictable results. You may also find that you will get false hits when one word of your search phrase is in one column of a row and the other word of your search phrase is in another column of the same row.
For example, assume you have two columns that are being Full‑Text Indexed. For a single row, the first column contains "James Bond" and the second column "Roger Moore." For the second row, the first column contains "James Moore" and the second column contains "Roger Bond." Doing a search on "James Bond" will return hits for both rows, as would a search on "Roger Moore."
Prefix and Generation
By default, a FREETEXT search does not do wild carding. It does do word stemming (generation), though by default, without your having to use the formsof(inflectional, word) operator. So if you have rows containing the words run, runs, ran, running, runner, runt, the query Select * from table_name where FREETEXT(column_name, 'run') will match with the rows containing run, runs, ran, running but not runt, or runner. You can tell FREETEXT to do wild carding in the same way that you tell it to do wildcarding using the CONTAINS predicate.
When you do a FREETEXT search, it is the functional equivalent of doing a CONTAINS search using the NEAR keyword between each search argument. In other words,
Select * from table_Name where CONTAINS(column_name, 'peanut ~ jelly')
will return the same results as
Select * from table_Name where FREETEXT(column_name, 'peanut jelly')
There are differences when you do a FREETEXTTABLE search.
As mentioned in the section on Phrase, by default a query using the FREETEXT predicate will return hits to all declensions of the search argument.
Select * from table_name where CONTAINS(coumn_name, 'formsof(inflectional,run)')
will return the same hits as
Select * from table_name where FREETEXT(coumn_name, 'run').
To do a weighted query in FREETEXT, you must use the ISABOUT keyword just like with CONTAINS.
designed by :: smilla group