Traditionally, retrieving specific text data from database columns or file systems has been a cumbersome and expensive process, often requiring third-party tools.
Microsoft® SQL Server™ provides a rich text data retrieval system. SQL Server 2000 offers an enhanced full-text search service that allows you to:
Populating or updating an index does not have to interfere with other tasks. Full-text index updates can be scheduled in the background using the Full-text Indexing wizard, SQL Server Enterprise Manager, or the SQL Server Agent job scheduler.
Depending on your data and resources, you can choose among the full rebuild, the timestamp-based incremental rebuild, and the change tracking methods to maintain your full-text indexes. The full rebuild method involves rescanning all rows. The timestamp-based incremental rebuild method only rescans those rows that have changed since the last rebuild (full or incremental) of the index. With the change tracking method, SQL Server maintains a list of all changes to the indexed data and you can use this list to update the full-text index. For more information, see Maintaining Full-text Indexes.
Using full-text search, you can index and query certain types of data stored in image columns. Full-text search uses one of several supported filters to interpret the data and extract the text data for indexing and querying. SQL Server provides filters for the .doc, .xls, .ppt, .txt, and .htm file extensions. For more information, see Filtering Supported File Types.
Once the image column is indexed, you can search the column using the search predicates CONTAINS and FREETEXT. For more information, see Using Full-text Predicates to Query Image Columns.
When you use the optional top_n_by_rank argument of the CONTAINSTABLE or FREETEXTTABLE rowset function in your query, SQL Server will only return the top ranked matches, up to the n number specified. For more information, see Limiting Result Sets in the Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued Functions.