Integrated Full-Text Search (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Full-text search refers to the functionality in Microsoft SQL Server that supports full-text queries against character-based data. These types of queries can include words and phrases, in addition to multiple forms of a word or phrase. To support full-text queries, you must implement full-text indexes on the columns referenced in the query. You can configure the columns with character data types or with binary data types. When binary data is indexed, another column in the table must indicate the type of data that is contained within the binary column.

A full-text index is made up of word tokens that are derived from the text being indexed, but ignores stopwords. A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-text index since they are known to be useless to a search. Although iFTS ignores the inclusion of stopwords, the full-text index does take into account their position.

Best Practices

The following resources provide examples of customer scenarios for integrated full-text search (iFTS), in addition to general iFTS reference material. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Case Studies and References

The following two case studies provide helpful information.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Understand the number and type of users, as well as their expected search patterns and response-time requirements. With one customer, response-time performance was critical because the users, who were lawyers, were investigating the depositions for arguing a well-known case. Ask customers about user expectations so that you can keep them in mind as the usual design trade-offs are made.

  • Review the article SQL Server 2008 Full-Text Search: Internals and Enhancements1 to get a good understanding of the capabilities.

  • Does the search application need to tightly integrate with relational queries and the relational structured data? If so, and if the relational predicate is very selective, it is optimal to have a predicate push-down plan. See the section Implementing Full-Text Search: Best Practices10 in the article "SQL Server 2008 Full-Text Search: Internals and Enhancements."

  • Does the search application need to integrate with Internet search or Microsoft SharePoint search?

  • Consider where the binary data resides, because FILESTREAM supports iFTS, but remote binary large object (BLOB) storage (RBS) does not.


Following are the full URLs for the hyperlinked text.

1 SQL Server 2008 Full-Text Search: Internals and Enhancements

2 Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

3 SQL Server Full-Text Search (FTS) Blog

4 Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage

5 Full-text query in local languages

6 Supplementary Characters

7 Best Practices for Choosing a Language When Creating a Full-Text Index

8 RSS Aggregator NewsGator Manages 2.5 Billion Articles with SQL Server 2008

9 FileControl Partners: Faster Searches of 1 Terabyte of Litigation Documents Gained with SQL Server 2005

10 Implementing Full-Text Search: Best Practices