Professional Microsoft® SQL Server® 2008 Programming

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:

• 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. These are generally even more inefficient, but 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 ...