Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued Functions

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 used differently from the full-text predicates, CONTAINS and FREETEXT.

Differentiating the full-text predicates from the functions

Although both the full-text predicates 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:

• CONTAINS and FREETEXT both return a TRUE or FALSE value, so they are typically specified in the WHERE clause 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. 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.

This 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.

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

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]

Comparison between CONTAINSTABLE and CONTAINS

The CONTAINSTABLE function and the CONTAINS predicate use similar search conditions.

However, in CONTAINSTABLE you specify the table that will be full-text searched, the column (or all the columns) in the table to be searched, and the search condition. A fourth parameter, an optional one, makes it possible for the user to indicate that only the highest specified number of matches be returned. For more information, see the Limiting Result Sets section.

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.

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            
---- ------------         -----------       -------            
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 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

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, 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

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).

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

Limiting Result Sets

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.

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 will be 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, here's how the query looks:

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]

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     

See Also

CONTAINSTABLE

FREETEXTTABLE