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
Title ---------------------------------------------- The Hunter and the Woodman
The main problem with performing SQL Server
WHERE...LIKE searches is the slow performance. Indexes are searchable from ...