Binaryworld - Full-text indexing: Advanced Queries ... [ SQL Server -> FullText ]

In our first two parts we covered creating a full-text index, updating the index and some basic queries. In this segment we'll cover some of the more advanced topics. The first is ranking the results we recieve. We do this using the CONTAINSTABLE statement. An sample query and result set are shown below.

Click here to copy the following block

Select [KEY], RANK, LastName
from ContainsTable(Employees, *, 'french or university') S,
Employees E
Where E.EmployeeID = S.[KEY]
Order by Rank desc

KEY     RANK    LastName
----------- ----------- --------------------
8      64     Callahan
2      32     Fuller
6      32     Suyama
5      32     Buchanan
9      32     Dodsworth
1      16     Davolio
7      16     King

(7 row(s) affected)
The highest ranking result matched the text "...the University of Washington. She has also completed a course in business French. She reads and writes French." Notice that "French" was in the text twice. The lowest ranking matched only the word university. Using AND, OR and NEAR you can create some complex queries.

CONTAINSTABLE actually returns a table with two columns, KEY and RANK. KEY contains the field we defined as our unique key when we build the full-text index on the table. RANK is how well this row matches the search criteria.

Using the KEY field you join back to your original table to return other values you might need for your query.