Pro Full-Text Search in SQL Server 2008
Published on 29. January 2009
312 pages
978-1-4302-1595-0 (ISBN)
Description
Businesses today want actionable insights into their data-they want their data to reveal itself to them in a natural and user-friendly form. What could be more natural than human language? Natural-language search is at the center of a storm of ever-increasing web-driven demand for human-computer communication and information access. SQL Server 2008 provides the tools to take advantage of the features of its built-in enterprise-level natural-language search engine in the form of integrated full-text search (iFTS). iFTS uses text-aware relational queries to provide your users with fast access to content. Whether you want to set up an enterprise-wide Internet or intranet search engine or create less ambitious natural-language search applications, this book will teach you how to get the most out of SQL Server 2008 iFTS:
- Introducing powerful iFTS features in SQL Server, such as the FREETEXT and CONTAINS predicates, custom thesauruses, and stop lists
- Showing you how to optimize full-text query performance through features like full-text indexes and iFilters
- Providing examples that help you understand and apply the power of iFTS in your daily projects
More details
Other editions
Additional editions
Hilary Cotter | Michael Coles
Pro Full-Text Search in SQL Server 2008
Book
12/2008
APress
€53.49
Available immediately
Persons
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.
Content
SQL Server Full-Text Search.- Administration.- Basic and Advanced Queries.- Client Applications.- Multilingual Searching.- Indexing BLOBs.- Stoplists.- Thesauruses.- iFTS Dynamic Management Views and Functions.- Filters.- Advanced Search Techniques.
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.
. . . 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.
