Full-text indexing: Advanced Queries

By Bill Graziano on 30 June 2000 | 0 Comments | Tags: Full-Text Indexing


In the third article on full-text indexing we'll talk about result rankings.

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

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using SQL Server 2005 fulltext search from ASP.NET 2.0 (5 February 2007)

INF: SQL Server 2000 Full-Text Search Deployment White Paper (Q323739) (10 June 2002)

Search Design (13 December 2000)

Full-text indexing: Overview and Installation (27 June 2000)

Other Recent Forum Posts

need help with summing multiple rows in columns (27 Replies)

How can I group student scores into quintile using (4 Replies)

rows to columns (8 Replies)

Msg 4104 (22 Replies)

Merge statement Insert into multiple tables (1 Reply)

Traditional Approach Vs Property Bag Tables Advice (5 Replies)

Slow Query (3 Replies)

dtexec issue in SSIS Package execution from SSMS (0 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -