FREETEXT and FREETEXTTABLE

FREETEXT and FREETEXTTABLE

The FREETEXT predicate and FREETEXTTABLE function searches automatically expand your search terms to include all noun conjugations (including plurals, gender, and case) and declensions (verb forms) of the root of the original search term. So a search for the word jog is expanded to a search for the following:

• jog
• jogging
• jogged

A search for the word book is expanded to include the following:

• book
• books
• booked
• booking

In the case of the word book, both verb and noun forms are included. FREETEXT queries also automatically apply all thesaurus expansions and replacements for the language being used in the query, as well as the global thesaurus entries for all languages. As an example, you might include thesaurus entries to expand the word jog to include the words run and sprint as well.

Another feature of a FREETEXT query is that nearness, or separation distance, is factored into rank. Nearness is a measure of how close individual search tokens are to one another in the matching content. By default, a multitoken search using the CONTAINS predicate will only return rows where the tokens or words are adjacent to one another—that is, there are no words in between tokens. You can override this behavior with the NEAR operator or by including a Boolean operator (AND, OR, or AND NOT) between search tokens. As an example, a default CONTAINS search for "University of California" won’t match with "University California", but in a FREETEXT search it will match.

A FREETEXT search accepts up to three arguments. They are, in order:

1. Column list.
   • You can qualify columns using two-part names.
   • You can use the wildcard * character to indicate that all full-text-indexed columns should be included in the search.
   • You can use parentheses to enclose a comma-separated list containing multiple column names.
2. Search phrase: should be a Unicode string using the appropriate FREETEXT or CONTAINS search predicate syntax.
3. Optional language setting specifier preceded by the LANGUAGE keyword.
   • If you don’t use the LANGUAGE clause, the default full-text language setting for your server is used.

SQL	Copy Code
/* FREETEXT search through all columns */ SELECT Nr, Title FROM vwtsContentPUB WHERE FREETEXT ( * /* Replace * with an indexed column name within vwtsContentPUB for searching in just one column */ , N'Smartsite iXperion' /* Explicitly use nvarchar will increase performance */ , LANGUAGE 1033 /* Use 1043 for Dutch */ );

SQL	Copy Code
/* FREETEXT search using multiple columns */ SELECT c.Nr, c.Title FROM vwtsContentPUB c WHERE FREETEXT ( (c.Title, c.Author, c.QueryData) , N'Seneca Smartsite' /* No language explicitly set means usage of default language during full-text indexing */ );

Returning the Top N by RANK

When large numbers of search results are returned, it may not be practical to transfer the entire result set to the client for client-side paging. You can use the top_n_by_rank argument to limit results in such cases. The top_n_by_rank argument is a fourth optional argument available only with the FREETEXTTABLE and CONTAINSTABLE functions.

SQL	Copy Code
/* Retrieving the top five search results by rank */ SELECT c.Nr, c.Title, k.[RANK] FROM vwtsContentPUB c INNER JOIN FREETEXTTABLE ( *, N'Smartsite iXperion', 5 ) k ON c.Nr = k.[KEY] ORDER BY k.[RANK] DESC;

In SQL Server 2005, there is an additional setting for "precompute rank" that gives a performance boost for FREETEXTTABLE queries in which the top_n_by_rank argument was used. This setting is deprecated in SQL 2008 and is not operational: it doesn’t do anything. This feature is no longer required, as the iFTS query engine is now fully integrated with SQL Server.