Lesson 1

Full-text Searching using SQL-Server

Another way that you can query your database is through SQL Server 2005 full-text searching, which allows you to query character-based data.
After completing this module, you will be able to:

Describe what full-text searching is and when to use it
Describe considerations for full-text searching
Practice implementing full-text searching

In the next lesson, you will get an overview of full-text searching.

Full-Text Catalogs

Full-text catalogs are mappings of data that speed the search for specific blocks of text within columns that have full-text searching enabled. Prior to SQL Server 2008, full-text catalogs were stored external to the database (thus creating some signifi cant backup and recovery issues). As of SQL Server 2008, full-text catalogs have been integrated into the main database engine and storage mechanisms. Due to their complex nature, full-text indexes are beyond the scope of this text.
Full-Text Search: This option lets you allow searching of the text within your database. This is a very useful tool for searching documents or other large text-based data and could be used if you were building a search engine.

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 the beginning of the word, so searching for LIKE 'word%' is fast, but LIKE '%word%' is terribly slow. Searching for strings within a string cannot use the b-tree structure of an index to perform a fast index seek so it must perform a table scan instead. It is like looking for all the instances of "Paul" in the telephone book. The phone book is not indexed by first name, so each page must be scanned.