Chapter 18. Looking at Things in Full: Full-Text Search
Full-Text Search is an area of significant architectural change in SQL Server 2008. While the core use and functionality hasn't changed all that much, the full-text features are far more integrated into the core of SQL Server as of this release. If you feel you are already familiar with full-text and are ready to skip this chapter, I would encourage you to at least browse the architectural changes and consider their ramifications on things like backup and recovery as well as expanded query result support.
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:
LIKEclause. 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
CHARINDEX. These are generally even more inefficient, but can allow us to do things that
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 ...