Microsoft KB Archive/240833

= FIX: Full-Text Search Support for TOP via CONTAINSTABLE and FREETEXTTABLE Clauses =

Article ID: 240833

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Enterprise Edition

-



This article was previously published under Q240833



BUG #: 56082 (SQLBUG_70)



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.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
It is recommended that you include only one table per Full-Text Catalog when this parameter is used because this new Top_N_Rank functions on a per Catalog basis.

Keywords: kbbug kbfix kbsqlprog KB240833

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.