Professional SQL Server™ 2005 Programming

Chapter 21. Looking at Things in Full: Full-Text Search

Using plain old T-SQL (without full-text functionality), our options for querying text information are somewhat limited. Indeed, we have only a couple of options:

• Use a LIKE clause. This is generally woefully inefficient, and is not able to utilize any kind of index structure unless your search pattern starts with an explicit value. If the search starts with a wildcard (say "%" or "_"), then SQL Server wouldn't know which spot in the index to begin with — any indexes become worthless.

• Use some other form of pattern matching, such as PATINDEX or CHARINDEX. This is generally even more inefficient, but this can allow us to do things that LIKE will not.

With Full-Text Search, however, we gain the ability to index the contents of the text — essentially keeping a word list that lets us know what words we can find and in what rows. In addition, we are not limited to just pattern-matching algorithms — we can search for the inflected forms of words. For example, we might use the word university but have SQL Server still find the word universities, or, even better, SQL Server can find a word like drunk when the word we asked for was drink. It's up to us to decide how precise we want to be, but even if the word we are searching for is located deep in the middle of a large text block, SQL Server can quickly find the rows that contain the word in question.

Full-Text Search, or FTS, supports any document type that is supported by Microsoft ...