FREETEXTTABLE (Transact-SQL)

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.

Transact-SQL Syntax Conventions

Syntax

FREETEXTTABLE (table , { column_name | (column_list) | * } 
          , 'freetext_string' 
     [ ,LANGUAGE language_term ] 
     [ ,top_n_by_rank ] )

Arguments

• table
  Is the name of the table that has been marked for full-text querying. table or viewcan be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved.

  For more information, see Transact-SQL Syntax Conventions (Transact-SQL). 

  table cannot specify a server name and cannot be used in queries against linked servers.

• column_name
  Is the name of the column to search that resides in table. Columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary(max) are valid columns for full-text searching.

• column_list
  Indicates that several columns, separated by a comma, can be specified. column_list must be enclosed in parentheses. Unless language_term is specified, the language of all columns of column_list must be the same.

• *
  Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string. Unless language_term is specified, the language of all full-text indexed columns in the table must be the same.

• freetext_string
  Is text to search for in the column_name. Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.

  Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word and will be discarded.

  Use of WEIGHT, FORMSOF, wildcards, NEAR and other syntax is not allowed. freetext_string is wordbroken, stemmed, and passed through the thesaurus. If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.

• LANGUAGE language_term
  Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the FREETEXTTABLE query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

  When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language. When specified as a hexadecimal value, language_term is 0x followed by the hexadecimal value of the LCID. The hexadecimal value must not exceed eight digits, including leading zeros.

  If the value is in double-byte character set (DBCS) format, Microsoft SQL Server will convert it to Unicode.

  If the language specified is not valid or there are no resources installed that correspond to that language, SQL Server returns an error. To use the neutral language resources, specify 0x0 as language_term.

• top_n_by_rank
  When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.

  If filtering is performed in addition to the FREETEXTTABLE predicate, the filter is applied to the top n rows and fewer than top_n_by_rank rows will be returned. Enabling the precompute rank option in the sp_configure stored procedure can increase the prerformance of FREETEXTTABLE queries that use the top_n_by_rank parameter. For more information, see sp_configure (Transact-SQL) and sp_fulltext_service (Transact-SQL).

Remarks

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.

FREETEXTTABLE is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel (Transact-SQL).

Permissions

FREETEXTTABLE can be invoked only by users with appropriate SELECT privileges for the specified table or the referenced columns of the table.

Examples

The following example returns the category name and description of all categories that relate to sweet, candy, bread, dry, or meat.

USE Northwind;
SELECT FT_TBL.CategoryName 
    ,FT_TBL.Description
    ,KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL 
    INNER JOIN FREETEXTTABLE(dbo.Categories, Description, 
        'sweetest candy bread and dry meat') AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO

The following example is identical and shows the use of the LANGUAGElanguage_term and top_n_by_rank parameters.

USE Northwind;
SELECT FT_TBL.CategoryName 
    ,FT_TBL.Description
    ,KEY_TBL.RANK
FROM dbo.Categories AS FT_TBL 
    INNER JOIN FREETEXTTABLE(dbo.Categories, Description, 
        'sweetest candy bread and dry meat',LANGUAGE 'English',2) 
        AS KEY_TBL
        ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO

Change History

See Also

Reference

CONTAINS (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
Rowset Functions (Transact-SQL)
SELECT (Transact-SQL)
WHERE (Transact-SQL)

Other Resources

Querying SQL Server Using Full-Text Search
precompute rank Option

Help and Information

Getting SQL Server 2005 Assistance