Pro Full-Text Search in SQL Server 2008 (eBook)

Hilary Cotter has been involved in information technology for more than 19 years. He has worked as a web and database consultant specializing in search solutions, and he has helped several major sites tune their e-commerce sites. Hilary has answered over 6,000 questions on the Microsoft newsgroups and founded, with Chris Crowe, both IISFAQ.com, the world's largest resource for information on Internet Information Server, and IndexServerFAQ.com, which averages 1.2 million hits per month. In 2001, Microsoft recognized him with a Microsoft SQL Server "Most Valuable Professional" award. Hilary received his bachelor's of applied science degree in mechanical engineering from the University of Toronto. He subsequently studied economics at the University of Calgary and computer science at the University of California, Berkeley.

A table of contents is not available for this title.

CHAPTER 1 SQL Server Full-Text Search (S. 19-20)

. . . but I still haven’t found what I’m looking for.
—Bono Vox, U2

Full-text search encompasses techniques for searching text-based data and documents. This is an increasingly important function of modern databases. SQL Server has had full-text search capability built into it since SQL Server 7.0. SQL Server 2008 integrated full-text search (iFTS) represents a significant improvement in full-text search functionality, a new level of full-text search integration into the database engine over prior releases. In this chapter, we’ll discuss full-text search theory and then give a high-level overview of SQL Server 2008 iFTS functionality and architecture.

Welcome to Full-Text Search

Full-text search is designed to allow you to perform linguistic (language-based) searches against text and documents stored in your databases. With options such as word and phrase-based searches, language features, the ability to index documents in their native formats (for example, Office documents and PDFs stored in the database can be indexed), inflectional and thesaurus generational terms, ranking, and elimination of noise words, full-text search provides a powerful set of tools for searching your data. Full-text search functionality is an increasingly important function in modern databases. There are many reasons for this increase in popularity, including the following:

• Databases are increasingly being used as document repositories. In SQL Server 2000 and prior, storage and manipulation of large object (LOB) data (textual data and documents larger than 8,000 bytes) was difficult to say the least, leading to many interesting (and often complicated) alternatives for storing and manipulating LOB data outside the database while storing metadata within the database. With the release of SQL Server 2005, storage and manipulation of LOB text and documents was improved significantly. SQL Server 2008 provides additional performance enhancements for LOB data, making storage of all types of documents in the database much more palatable. We’ll discuss these improvements in later chapters in this book.

• Many databases are public facing. In the not too distant past, computers were only used by a handful of technical professionals: computer scientists, engineers, and academics. Today, almost everyone owns a computer, and businesses, always conscious of the bottom dollar, have taken advantage of this fact to save money by providing self-service options to customers. As an example, instead of going to a brick-and-mortar store to make a purchase, you can shop online, instead of calling customer service, you check your orders online, instead of calling your broker to place a stock trade, you can research it and then make the trade online. Search functionality in public-facing databases is a key technology that makes online self-service work.

• Storage is cheap. Even as hard drive prices have dropped, the storage requirements of the average user have ballooned. It’s not uncommon to find a half terabyte (or more) of storage on the average user’s personal computer. According to the Enterprise Strategy Group Inc., worldwide total private storage capacity will reach 27,000 petabytes (27 billion gigabytes) of storage by 2010. Documents are born digitally, live digitally, and die digitally, many times never having a paper existence, or at most a short transient hard-copy life.