TABLE 1: Comparison of Full-Text Search and a Custom Solution
CategoryFull-Text SearchCustom Solution
Scaling out search functionalityYou must stop the service and copy files (an undocumented procedure) or rebuild entire indexes on multiple servers.Tables reside in database and you can replicate them to other servers by using normal replication.
Disaster recoveryYou must back up search files and restore them separately; alternatively, you can rebuild indexes from scratch.You can use normal database disaster recovery procedures.
Performance tuningImplementation details are hidden; limited performance tuning is possible.You can use normal database performance tuning procedures.
Data cachingIn one test, full-text search went to disk 100 times for 100 identical, sequential searches.SQL Server's query optimizer can use data cache.
ReliabilityService too busy errors could cause a complete server reboot.Custom solution is as reliable as SQL Server.
Customizing stemming, searching for similar words (e.g., run, runs, running) Impossible.Possible, but requires development effort.
Performance under loadQuestionable; occasional Service too busy errors.Treats search as just another SQL Server query.
Scope of noise wordsAll full-text tables and searches use one noise word file.Different searches and tables can use different sets of noise words depending on your implementation.
Index populationSeveral choices: log based, partial population, or full rebuilds. Repopulations can be scheduled, manually started, or background.Flexibility requires development and testing effort depending on your implementation.
ImplementationStraightforward; includes reusable stored procedures and good documentation.You must implement everything from scratch.
Ranking results by applicabilityPossible through RANK feature.Requires development.
Handling multiple document types (Word, RTF, HTML)Supported well in SQL Server 2000 with document type columns.Difficult and requires a great deal of development effort.