The CONTAINSTABLE and FREETEXTTABLE functions are used to specify full-text queries that return relevance rankings for each row. Although both the full-text predicates (CONTAINS and FREETEXT) and the full-text rowset-valued functions are used for full-text queries, and the Transact-SQL statement 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:
CONTAINSTABLE and FREETEXTTABLE both return a table of zero, one, or more rows, so they must always be specified in the FROM clause.
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 and the values returned in the KEY column are the full-text key values 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 between 0 and 1000. These values are used to rank the rows returned according to how well they met the selection criteria.
Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.
The following example shows how such queries can be written to use rank. In this example, the contents of some documents reside in the DocText column of the doc_collection table, and the table also contains StorName, Size, and DocAuthor columns. The query obtains the following:
The unique key column for the table is DocNo. The rows in the result set are to be ordered so that those with the highest rank value are returned first.
SELECT Q.StorName, Q.Size, Q.DocAuthor, W.Citizenship
FROM doc_collection AS Q,
Writers AS W,
ContainsTable(doc_collection, DocText, '"SQL Server" ~ text') AS K
WHERE Q.DocAuthor = W.writer_name
AND K.[KEY] = Q.DocNo
ORDER BY K.RANK DESC
The format of CONTAINSTABLE is almost the same as the format of CONTAINS. The only difference is that table name is inserted as the first parameter. Thus, the second parameter is the name of the column being searched, and the third parameter is the full-text search condition. The syntax of the full-text search condition is exactly the same for CONTAINS and for CONTAINSTABLE.
This query specifies using CONTAINSTABLE to return a rank value for each row.
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]
Here is the result set:
RANK CompanyName ContactName address
---- ------------ ----------- -------
15 Maison Dewey Catherine Dewey Rue Joseph-Bens 532
15 Victuailles en stock Mary Saveley 2, rue du Commerce
123 Bon app' Laurence Lebihan 12, rue des Bouchers
15 Mère Paillarde Jean Fresnière 43 rue St. Laurent
65 Du monde entier Janine Labrune 67, rue des Cinquante Otages
15 France restauration Carine Schmitt 54, rue Royale
15 Spécialités du monde Dominique Perrier 25, rue Lauriston
15 Vins et alcools Paul Henriot 59 rue de l'Abbaye
Chevalier
15 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine
The following query extends a FREETEXT 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, 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]
ORDER BY KEY_TBL.RANK DESC
GO
The only difference in the syntax of FREETEXTTABLE and FREETEXT is the insertion of the table name as the first parameter.
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, 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
Queries that use rowset-valued functions are complicated because 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 number of the column that has been selected for enforcing unique rows for the table. This example shows how the name of the key column can be obtained and used programmatically.
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
')
GO
You can avoid the complexity of using CONTAINSTABLE and FREETEXTTABLE by writing stored procedures that accept a few facts about the query and then create and execute the appropriate query. A simplified procedure that submits a FREETEXTTABLE query follows. The table shows the procedure parameters (all input).
| Parameter | Required | Description |
|---|---|---|
| @select_list | Yes | KEY_TBL.RANK can be one of the columns specified. |
| @from_table | Yes | |
| @freetext_column | Yes | |
| @treetext_search | Yes | Search condition. |
| @additional_predicates | Optional | If there are any, these get added with AND after the FREETEXT predicate. KEY_TBL.RANK can be used within expressions. |
| @order_by_list | Optional | KEY_TBL.RANK can be one of the columns specified. |
The code for the procedure is:
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
)
END
This procedure 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
GO
| CONTAINSTABLE | FREETEXTTABLE |