CONTAINSTABLE() Rowset-Valued Function

The CONTAINSTABLE() function is used to issue contains-type full-text queries that return relevance rankings for each row.

The CONTAINSTABLE syntax is as follows, where table_ref refers to a full-text registered table, column_ref: refers to a specific full-text registered column, or * refers to all columns in table_ref that are full-text registered.

contains_condition:

Is the same as described for the CONTAINS predicate.

Although both the CONTAINS predicate and CONTAINSTABLE() function are used for contains-type full-text queries, and the SQL used to specify the full-text search condition is the same in both, there are major differences in the way these are used:

• CONTAINS returns a true or false value, and so typically is specified in the WHERE clause of a SELECT statement.

  CONTAINTABLE() returns a table of zero, one, or more rows, and so always must be specified in the FROM clause.

• CONTAINS can be used only to specify selection criteria that SQL Server uses to determine the membership of the result set.

  CONTAINSTABLE() is 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 that has values 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 contains_condition. Furthermore, the table produced by the CONTAINSTABLE() function has a column named rank that contains values from zero to 1,000 that can be used to rank the returned rows according to how well they meet the selection criteria.

Queries that use the CONTAINSTABLE() function are more complex than those using the CONTAINS predicate because it is necessary to explicitly join qualifying rows returned by CONTAINSTABLE() with the rows in table_ref.

For example, the contents of some documents reside in the DocText column of the doc_collection table, and the table also contains the StorName, Size, and DocAuthor columns. The unique key column for the table is named DocNo. You want the rows in the result set 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" NEAR() text'

                  ) AS K

WHERE Q.DocAuthor = W.writer_name

  AND K.[KEY] = Q.DocNo

ORDER BY K.RANK DESC

To simplify the use of CONTAINSTABLE() for queries that involve only one table and no grouping, use the following fixed template:

SELECT select_list ,KEY_TBL.RANK

FROM table_ref AS FT_TBL

CONTAINSTABLE (     table_ref

                ,   { column_ref | * }

                , ' contains_condition ' )

) AS KEY_TBL

WHERE FT_TBL.key_column = KEY_TBL.[KEY]

      AND predicate  ...

ORDER BY KEY_TBL.RANK DESC

The following example uses the previous template. In this example, the wording on the plaques in the Hockey Hall of Fame reside in the PlaqueWording column of HockeyHall table, and the table also contains the PlayerName, StartYear, and LastYear columns. The unique key column for the table is named PlaqueNo. You want to return the PlayerName, PlaqueNo, and rank values for players who might have played for the Kenora (also called Rat Portage) teams in the early 1900s. The higher ranking rows should be returned first.

SELECT PlayerName, PlaqueNo, KEY_TBL.RANK

FROM HockeyHall AS FT_TBL

     CONTAINSTABLE(HockeyHall, PlaqueWording,

                   ' CKenora OR "Rat Portage" '

                  ) AS KEY_TBL

WHERE FT_TBL.PlaqueNO = KEY_TBL.[KEY]

  AND StartYear < 1915 AND EndYear > 1899 

ORDER BY KEY_TBL.RANK DESC