CONTAINSTABLE and FREETEXTTABLE functions are used to specify full-text queries that return relative rankings for each row. These two functions are similar to full-text predicates CONTAINS and FREETEXT, but they are used differently.
Distinguishing Full-text Predicates from Functions
Although full-text predicates and full-text assignment row set functions are used for full-text queries, and the Transact-SQL statements used to specify full-text retrieval conditions are the same, there are still major differences in their usage methods:
CONTAINS and FREETEXT both return TRUE or FALSE values, so they are generally specified in the WHERE clause of SELECT statements.
CONTAINSTABLE and FREETEXTTABLE both return a table with 0, 1, 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 membership of result sets.
CONTAINSTABLE and FREETEXTTABLE are also used to specify selection criteria. The returned table has a column named KEY, which contains the full-text key values. Each full-text registry contains such columns, in which the values must be unique. In the full-text registry returned by CONTAINSTABLE or FREETEXTTABLE, the value in the KEY column is the only value of the row that matches the selection criteria specified in the full-text retrieval criteria.
Moreover, the tables generated by CONTAINSTABLE and FREETEXTTABLE also have a column named RANK, which contains values from 0 to 1000. These values are used to sort the returned rows according to the degree to which they meet the selection criteria.
Queries using CONTAINSTABLE and FREETEXTTABLE functions are more complex than those using CONTAINS and FREETEXT predicates because the required rows returned by the function must be explicitly joined to the rows in the original SQL Server table.
For the Description column containing sweet and savory near the word "sauces" or "candies", the following example returns the description and classification name of all its food classifications. All rows with the "Seafood" category name will be ignored. Only rows with a rank value of 2 or higher are returned.
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 DESCFor the Description column containing sweet and savory near the word "sauces" or "candies", the following example returns the descriptions and classification names of the top 10 foods.
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]Comparison between CONTAINSTABLE and CONTAINS
The CONTAINSTABLE function and the CONTAINS predicate use similar retrieval conditions.
However, in CONTAINSTABLE, you specify the table to be retrieved in full text, the column (or all columns) to be retrieved in the table, and the retrieval conditions. Another parameter is an optional parameter that allows the user to indicate that only the maximum number of matches are returned. For more information, see the Restricted Result Set section.
CONTAINSTABLE returns a table containing a column named RANK. This RANK column contains the values of each row representing the degree to which the row matches the selection criteria.
This query uses CONTAINSTABLE to specify the ranking value for each row to be returned.
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)
)
'
) AS K
ON C.CustomerID = K.[KEY]The following 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
15 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine
15 Maison Dewey Catherine Dewey Rue Joseph-Bens 532
15 Mère Paillarde Jean Fresnière 43 rue St. Laurent
15 Spécialités du monde Dominique Perrier 25, rue Lauriston
15 Vins et alcools Paul Henriot 59 rue de l'Abbaye
Chevalier
15 Victuailles en stock Mary Saveley 2, rue du Commerce
Comparison between FREETEXTTABLE and FREETEXT
The following query expands the FREETEXTTABLE query to return the highest ranked rows first, and adds the ranking of each row to the selection list. To specify this query, you must know that Category ID is the only key column in 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
GOThe only grammatical difference between FREETEXTTABLE and FREETEXT is to insert the table name as the first parameter.
The following is an extension of the same query, which returns only rows with a ranking value of 10 or more:
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
GOIdentify unique key column names
Queries using the assignment row set function are complex because the name of the unique key column must be known. Each table that enables full-text queries has the TableFulltext KeyColumn attribute, which contains the ID number of the column selected to implement a unique row in the table. This example shows how to programmatically obtain and use key column names.
USE Northwind
GO
DECLARE @key_column sysname
SET @key_column = Col_Name(Object_Id('Categories'),
ObjectProperty(Object_id('Categories'),
'TableFulltextKeyColumn')
)
print @key_column
EXECUTE ('SELECT Description, KEY_TBL.RANK
FROM Categories FT_TBL
INNER JOIN
FreetextTable (Categories, Description,
''How can I make my own beers and ales?'') AS KEY_TBL
ON FT_TBL.'
+
@key_column
+
' = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC
')
GOYou can write a stored procedure that accepts some facts about the query to avoid the complexity of using CONTAINSTABLE and FREETEXTTABLE, and then you can create and execute the corresponding query. The following is a simplified process that submits a FREETEXTTABLE query. The following table shows the process parameters (all inputs).
Necessary description of parameters
@ Addi_predicates is optional, if any, after the FREETEXT predicate with AND. KEY_TBL.RANK can be used inside an expression.
@ freetext_column is
@ freetext_search is the search condition.
@ from_table is
@ order_by_list optional KEY_TBL.RANK can be one of the specified columns.
< select_list > KEY_TBL.RANK can be one of the specified columns.
The code for this process is as follows:
CREATE PROCEDURE freetext_rank_proc
@select_list nvarchar(1000),
@from_table nvarchar(517),
@freetext_column sysname,
@freetext_search nvarchar(1000),
@additional_predicates nvarchar(500) = '',
@order_by_list nvarchar(500) = ''
AS
BEGIN
DECLARE @table_id integer,
@unique_key_col_name sysname,
@add_pred_var nvarchar(510),
@order_by_var nvarchar(510)
-- Get the name of the unique key column for this table.
SET @table_id = Object_Id(@from_table)
SET @unique_key_col_name =
Col_Name( @table_id,
ObjectProperty(@table_id, 'TableFullTextKeyColumn') )
-- If there is an additional_predicate, put AND() around it.
IF @additional_predicates <> ''
SET @add_pred_var = 'AND (' + @additional_predicates + ')'
ELSE
SET @add_pred_var = ''
-- Insert ORDER BY, if needed.
IF @order_by_list <> ''
SET @order_by_var = 'ORDER BY ' + @order_by_var
ELSE
SET @order_by_var = ''
-- Execute the SELECT statement.
EXECUTE ( 'SELECT '
+ @select_list
+ ' FROM '
+ @from_table
+ ' AS FT_TBL, FreetextTable('
+ @from_table
+ ','
+ @freetext_column
+ ','''
+ @freetext_search
+ ''') AS KEY_TBL '
+ 'WHERE FT_TBL.'
+ @unique_key_col_name
+ ' = KEY_TBL.[KEY] '
+ @add_pred_var
+ ' '
+ @order_by_var
)
ENDThis process can be used to submit the query:
USE Northwind
GO
EXECUTE freetext_rank_proc
'Description, KEY_TBL.RANK', -- Select list
'Categories', -- From
'Description', -- Column
'How can I make my own beers and ales?', -- Freetext search
'KEY_TBL.RANK >= 10', -- Additional predicate
'KEY_TBL.RANK DESC' -- Order by
GORestricted result set
In many full-text queries, there are many items that meet the search criteria. To prevent queries from returning too many matches, you can use the optional parameter top_n_by_rank in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to the rank you want to return.
With this information, Microsoft < SQL Server} will sort the matches by rank value, and then return only the matches that reach the specified number. This choice can greatly improve performance. For example, for a query that normally returns 100,000 rows from a table of one million rows, the query processing speed would be faster if only the first 100 rows were required to be returned.
If in the previous example using CONTAINSTABLE, only the first three matches are wanted to be returned, the form of the query is as follows:
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]The following 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
See also
CONTAINSTABLE
FREETEXTTABLE