Microsoft® SQL Server® 2008 Bible

Chapter 19. Using Integrated Full-Text Search

IN THIS CHAPTER

• Setting up full-text index catalogs with Management Studio or T-SQL code

• Maintaining full-text indexes

• Using full-text indexes in queries

• Performing fuzzy word searches

• Searching text stored in binary objects

• Full-text search performance

Several years ago I wrote a word search for a large database of legal texts. For word searches, the database parsed all the documents and built a word-frequency table as a many-to-many association between the word table and the document table. It worked well, and word searches became lightning-fast. As much fun as writing your own word search can be, fortunately, you have a choice.

SQL Server includes a structured word/phrase indexing system called Full-Text Search. More than just a word parser, Full-Text Search actually performs linguistic analysis by determining base words and word boundaries, and by conjugating verbs for different languages. It runs circles around the simple word index system that I built.

ANSI Standard SQL uses the LIKE operator to perform basic word searches and even wildcard searches. For example, the following code uses the LIKE operator to query the Aesop's Fables sample database:

USE Aesop;

SELECT Title
  FROM Fable
  WHERE Fabletext LIKE '%Lion%'
    AND Fabletext LIKE '%bold%';

Result:

Title
----------------------------------------------
The Hunter and the Woodman

The main problem with performing SQL Server WHERE...LIKE searches is the slow performance. Indexes are searchable from ...