precompute rank Option

Use the precompute rank server-level advanced option to improve the performance of FREETEXTTABLE queries that specify the top_n_by_rank parameter. This option is an optimization strategy that can reduce the use of CPU resources when SQL Server computes rank for FREETEXTTABLE at query time. When the option is set to 1, FREETEXTTABLE queries specified with top_n_by_rank use precomputed rank data stored in the full-text catalogs.

There is a significant difference in the type of rank computation that is used for FREETEXTTABLE queries and the rank computation that is used for CONTAINSTABLE queries. Because the precomputed rank optimization in SQL Server 2005 enables FREETEXTTABLE queries to use rank values stored in a full-text catalog instead of having to calculate those values dynamically, these queries should increase to near CONTAINSTABLE speed the FREETEXTTABLE queries that use the top_n_by_rank parameter.

When you use precomputed rank data, the result set that is returned by top_n_by_rank may not contain the same results as those that are returned when the precompute rank option is set to 0. The rank that is precomputed and stored in the full-text catalog when the precompute rank option is set to 1 may differ from the rank that is computed dynamically when the precompute rank option is set to 0.

Viewing and Setting the precompute rank Option

By default, this option is set to 0, FALSE. To view or set the status of the precompute rank option, use the following script.

-- To view advanced server-level options, first run this script if the 
-- show advanced option is not already set to 1, TRUE.
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE;
GO

-- To view the status of the precompute rank option
EXEC sp_configure;
GO

-- Or
SELECT *
FROM sys.configurations
WHERE name = N'precompute rank';
GO

-- To set the precompute rank option to TRUE
EXEC sp_configure 'precompute rank', '1';
GO
RECONFIGURE;
GO

Change History

See Also

Other Resources

FREETEXTTABLE (Transact-SQL)
sp_configure (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance