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 FabletextLIKE '%Lion%'
AND FabletextLIKE '%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 ...
Get Microsoft® SQL Server® 2008 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.