FIX: Full-Text Search Support for TOP via CONTAINSTABLE and FREETEXTTABLE Clauses (240833)

SYMPTOMS

Customers who have large Full-Text-enabled tables (usually, greater than 1 million rows) and have a need to restrict the results to the TOP number or percentage of rows returned can use a new syntax, Top_N_Rank, that has been included with CONTAINSTABLE and FREETEXTTABLE Full-Text Search query predicates. Also, Full-Text Search query performance is improved (as measured as number of rows returned per second) using this new syntax.

SQL Server 7.0 service packs later than Service Pack 1 will have the syntax for CONTAINSTABLE and FREETEXTTABLE extended as shown below:

   CONTAINSTABLE (table, {column | *}, 'contains_search_condition',{top_n_rank})

   FREETEXTTABLE (table, {column | *}, 'freetext_string',{top_n_rank})
				

where top_n_rank is an integer indicating that the "Top N by Rank" rows that meet the contains-string are to be returned.

It is the application's responsibility to determine what value of N is to be used. For example, for a query with a non-full-text predicate:

SELECT TOP 200 T.* FROM TableWithFTColumn as T,   CONTAINSTABLE(TableWIthFTColumn,*,'test',300) as CT
   WHERE  T.key=CT.key  AND T.a > 5
   ORDER BY CT.rank
				

It is up to the application to determine how many rows to request from CONTAINSTABLE. If too few are requested, the T.a > 5 predicate may filter out so many that fewer than 200 rows are returned even though there would be more matches if a higher Top-N-Rank value were used.