Full Text Searching  «Prev  Next»
Lesson 6

Full-text Searching Conclusion

This module discussed how and when to use full-text searching. Full-text searching is a very powerful new tool in SQL Server 7 that allows you to search rapidly on very large text fields. Having completed this module, you should be able to:
  1. Describe what full-text searching is and when to use
  2. Describe considerations for full-text searching
  3. Practice implementing full-text searching
In the next module, you will learn how cursors can help you manipulate data in SQL Server record sets.

Full text (3): The Full text events track only Full-Text Search crawl activity. There are no events for Full-Text Search configuration changes. More information about Full-Text Search queries is hidden in the Performance: FullTextQuery events.

(iFTS) Integrated Full-Text Search

Integrated Full-Text Search (iFTS) extends SQL Server beyond the traditional relational data searches by building an index of every significant word and phrase. In addition, the full-text search engine adds advanced features such as the following:
  1. Searching for one word near another word
  2. Searching with wildcards
  3. Searching for inflectional variations of a word (such as run, ran, running)
  4. Weighting one word or phrase as more important to the search than another word or phrase
  5. Performing fuzzy word/phrase searches
  6. Searching character data with embedded binary objects stored with SQL Server
  7. Using Full-Text Search in the WHERE clause or as a data source like a subquery

History of Full-Text Search

The history of Full-Text Search began in late 1998 when Microsoft reengineered one of its search engines (Site Server Search, designed for websites) to provide search services for SQL Server 7. The engine was called MSSearch, and it also provided search services to Exchange Content Indexing and SharePoint Portal Server 2001. I liked Full-Text Search when it was first introduced back in SQL Server 7, and I am glad that it is still here and Microsoft is continuing to invest in it. Microsoft continued to improve iFTS’s performance and scalability with SQL Server 2000 and SQL Server 2005. Also, in case you did not follow the evolution of Full-Text Search back in SQL Server 2005, Microsoft worked on bringing Full-Text Search closer to industry standards:

  1. The list of noise words was renamed to the industry standard term of stoplist .
  2. The many set-up stored procedures were simplified into normal DDL CREATE, ALTER, and DROP commands.

SQL Server 2008

With SQL Server 2008, the old stored procedure methods of setting up Full-Text Search are deprecated, meaning they will be removed in a future version. SQL 2008 Integrated Full-Text Search (iFTS) is the fourth-generation search component for SQL Server, and this new version is by far the most scalable and feature-rich. SQL 2008 iFTS ships in the Workgroup, Standard, and Enterprise versions of SQL Server. With SQL Server 2008, SQL Server is no longer dependent on the indexing service of Windows. Instead, it is now fully integrated within SQL Server, which means that the SQL Server development team can advance Full-Text Search features without depending on a release cycle.
The integration of FTS in the SQL engine should also result in better performance because the Query Optimizer can make an informed decision whether to invoke the full-text engine before or after applying non-FTS filters. Minor enhancements include the following:
  1. A number of new DMVs expose the workings of iFTS
  2. Forty new languages
  3. Noise words management with T-SQL using create fulltext stoplist
  4. Thesaurus stored in system table and instance-scoped

Full Text Searching - Quiz

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Full Text Searching - Quiz