SQL Server 2000 - Setup full-text search

Setup full-text search
Alexander Chigrik

Introduction
General concepts
Full-text indexes vs. regular SQL indexes
Maintaining full-text indexes
SQL Server 2000 full-text search enhancements
Step by step example
Literature


Full-text indexes vs. regular SQL indexes
The full-text indexes very differ from the regular SQL Server indexes. The full-text indexes are stored in the full-text catalogs outside of SQL Server databases and managed by the MSSearch service. Full-text catalog is a set of operation system files (the default directory determined during installation is Ftdata subdirectory in the Microsoft SQL Server directory; for example, C:\MSSQL7\Ftdata, the default directory for SQL Server 7.0; and C:\Program Files\Microsoft SQL Server\Mssql\Ftdata, the default directory for SQL Server 2000).Unlike regular SQL indexes, only one full-text index per table is allowed. 249 nonclustered and 1 clustered regular SQL indexes are allowed per table.Unlike regular SQL indexes, full-text indexes are not updated automatically when the data upon which they are based is inserted, updated, or deleted. To reflect these changes, you should update full-text indexes manually, or create a job to update these indexes on a scheduled basis.Unlike regular SQL indexes, full-text indexes cannot be created, managed, or dropped using the Transact-SQL statements, only by using SQL Server Enterprise Manager, wizards, or stored procedures. For example, DROP INDEX and CREATE INDEX statements are not allowed for full-text indexes.


SQL Server 2000 full-text search enhancements
SQL Server 2000 introduces a new way to maintain the full-text indexes. There is Change tracking. Change tracking population maintains a log of all changes to the full-text indexed data, and propagates the changes to the full-text index. There are three Change tracking population's options:
Background
On demand
ScheduledWith the Background option, changes to rows in the table are propagated to the full-text index as they occur. You can use this option only when you have enough CPU and memory, because it can take many time.With the On demand option, you should manually update the full-text index by using the sp_fulltext_table with the update_index for the @action parameter.With the Scheduled option, you can use SQLServerAgent to schedule periodic jobs that execute the sp_fulltext_table system stored procedure with the update_index for the @action parameter.This is the example to start the Change tracking with the Background option for the Product table in the Sales database:
USE Sales
GO
EXEC sp_fulltext_table 'Product', 'Start_change_tracking'
EXEC sp_fulltext_table 'Product', 'Start_background_updateindex'
GO
Note. The Change tracking does not track any WRITETEXT or UPDATETEXT operations.Another SQL Server 2000 full-text search enhancement is image filtering. Image filtering allows you to index and query documents stored in image columns (only the text data stored in image columns can be indexed, images or pictures cannot be indexed).Note. Though a single computer can have multiple instances of SQL Server 2000, only one MSSearch service can exist. So, a single MSSearch service manages the full-text indexes for all the instances of SQL Server 2000 on the computer.


Literature
1. Full-Text Indexes
2. Full-Text Indexing Support
3. Full-Text Query Architecture
4. Maintaining Full-Text Indexes
5. Implementation of Full-text Search