Is a function used in the FROM clause of a Transact-SQL SELECT statement to perform a SQL Server full-text search on full-text indexed columns containing character-based data types. This function returns a table of zero, one, or more rows for those columns containing values that match the meaning and not just the exact wording, of the text in the specified freetext_string. FREETEXTTABLE is referenced as if it were a regular table name.
FREETEXTTABLE is useful for the same kinds of matches as the FREETEXT (Transact-SQL),
Queries using FREETEXTTABLE return a relevance ranking value (RANK) and full-text key (KEY) for each row.
For information about the forms of full-text searches that are supported by SQL Server, see Query with Full-Text Search.
Full-text predicates and functions work on a single table, which is implied in the FROM predicate. To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables.
FREETEXTTABLE uses the same search conditions as the FREETEXT predicate.
Like CONTAINSTABLE, the table returned has columns named KEY and RANK, which are referenced within the query to obtain the appropriate rows and use the row ranking values.
A. Using FREETEXT in an INNER JOIN
The following example returns the category name and description of all categories that relate to sweet, candy, bread, dry, or meat.
USE AdventureWorks2012 GO SELECT FT_TBL.Description ,KEY_TBL.RANK FROM Production.ProductDescription AS FT_TBL INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description, 'high level of performance') AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY] ORDER BY RANK DESC GO
B. Specifying Language and Highest Ranked Matches
The following example is identical and shows the use of the LANGUAGE language_term and top_n_by_rank parameters.
USE AdventureWorks2012 GO SELECT FT_TBL.Description ,KEY_TBL.RANK FROM Production.ProductDescription AS FT_TBL INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description, 'high level of performance', LANGUAGE N'English', 2) AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY] ORDER BY RANK DESC GO
The LANGUAGE language_term parameter is not required to use the top_n_by_rank parameter.