Comparing Full-Text Functions and Full-Text Predicates
The CONTAINSTABLE and FREETEXTTABLE functions are used to specify full-text queries that return relevance rankings for each row. These functions are very similar but are used differently from the full-text predicates, CONTAINS and FREETEXT.
Although both the full-text predicates and the full-text functions are used for full-text queries, and the syntax used to specify the full-text search condition is the same in both the predicates and the functions, there are major differences in the way that these are used. The following lists some important points of similarity and difference:
- CONTAINS and FREETEXT both return a TRUE or FALSE value and are specified in the WHERE or HAVING clauses of a SELECT statement.
- CONTAINSTABLE and FREETEXTTABLE both return a table of zero, one, or more rows, so they must always be specified in the FROM clause.
- CONTAINS and FREETEXT can only be used to specify selection criteria, which Microsoft SQL Server uses to determine the membership of the result set.
- CONTAINSTABLE and FREETEXTTABLE are also used to specify selection criteria. The table returned has a column named KEY that contains full-text key values. Each full-text registered table has a column whose values are guaranteed to be unique. The values returned in the KEY column of CONTAINSTABLE or FREETEXTTABLE are the unique values, from the full-text registered table, of the rows that match the selection criteria specified in the full-text search condition.
Furthermore, the table produced by CONTAINSTABLE and FREETEXTTABLE has a column named RANK, which contains values from 0 through 1000. A lower value indicates lower relevance. These values are used to rank the rows returned according to how well they matched the selection criteria.
Note
The rank value only indicates a relative order of relevance of the rows in the result set. The actual value is of no importance and should not be expected to be same each time the query is run. For more information about ranking, see Understanding Ranking.
CONTAINS and FREETEXT queries do not return any rank values.
When running queries that use the CONTAINSTABLE and FREETEXTTABLE functions qualifying rows returned must be explicitly joined with the rows in the original SQL Server table.
The following example returns the description and category name of all food categories for which the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies." All rows with a category name "Seafood" are disregarded. Only rows with a rank value of 2 or higher are returned.
Note
To run some of the examples in this topic, you will need to install the Northwind database. For information on how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.
USE Northwind;
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO
Comparison between CONTAINSTABLE and CONTAINS
The CONTAINSTABLE function and the CONTAINS predicate use similar search conditions.
However, in CONTAINSTABLE you specify the table that is full-text searched, the column (or all the columns) in the table to be searched, and the search condition. There is an optional parameter that allows the user to indicate that only the highest specified number of matches be returned. For more information, see the "Limiting Result Sets" section of this topic.
CONTAINSTABLE returns a table that includes a column named RANK. This RANK column contains a value for each row that indicates how well a row matched the selection criteria. The higher the rank value of a row the more relevant is the row for the given full-text query.
Comparison between FREETEXTTABLE and FREETEXT
The following query extends a FREETEXTTABLE query to return the highest ranked rows first and to add the ranking of each row to the select list. To specify the query, you must know that CategoryID is the unique key column for the Categories table.
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO
Here is an extension of the same query that only returns rows with a rank value of 10 or greater:
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories FT_TBL
INNER JOIN
FREETEXTTABLE (Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC;
GO
Identifying the Unique Key Column Name
When writing queries that use rowset-valued functions, it is necessary to know the name of the unique key column. Each full-text enabled table has the TableFulltextKeyColumn property that contains the column ID of the column that has been selected for enforcing unique rows for the table. This example shows how to obtain the name of the key column programmatically.
USE AdventureWorks;
GO
DECLARE @key_column sysname
SET @key_column = Col_Name(Object_Id('Production.Document'),
ObjectProperty(Object_id('Production.Document'),
'TableFulltextKeyColumn')
)
SELECT @key_column AS 'Unique Key Column';
GO
Limiting Result Sets to Return the Most Relevant Results
In many full-text queries, the number of items matching the search condition is very large. To prevent queries from returning too many matches, use the optional argument, top_n_by_rank, in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to rank you want returned.
Note
Using the top_n_by_rank argument returns a subset of rows that satisfy the full-text query. If top_n_by_rank is combined with other predicates, the query could return fewer rows than the number of rows that actually match all the predicates.
With this information, Microsoft SQL Server orders the matches by rank and returns only up to the specified number. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.
If you want only the top 3 matches returned on an earlier example using CONTAINSTABLE, the query looks like the following:
USE Northwind;
GO
SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON C.CustomerID = K.[KEY];
GO
Here is the result set:
RANK CompanyName ContactName address
---- ------------ ----------- -------
123 Bon app' Laurence Lebihan 12, rue des Bouchers
65 Du monde entier Janine Labrune 67, rue des Cinquante Otages
15 France restauration Carine Schmitt 54, rue Royale
This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];
GO
See Also
Other Resources
CONTAINSTABLE (Transact-SQL)
FREETEXTTABLE (Transact-SQL)