By: MSSQLTips | Comments | Related: > Full Text Search
Problem
Finding information in large text or Binary Large Object (BLOB) columns is a daunting task. Using the LIKE keyword, although effective in most cases, will not always find the needed information. Full-text searching and indexing is a useful yet under-utilized feature of SQL Server that assists developers and users in finding the information. Versions prior to 2005 use a shared service to provide full-text functionality, making the process inefficient. The backup and restore process was also tedious and separate from the usual database backup process. In SQL Server 2005 Microsoft designed a new version of the engine, called Microsoft Full-Text Engine for SQL Server (MSFTESQL) to make full-text searching and indexing more efficient and easier to maintain.
Solution
Full-Text Search Concepts
In general, full-text searching technologies use a number of components to accomplish its mission. Word breaker is a component that determines where a word begins and ends. A stemmer determines the root form of a given word (strands and stranded =strand). In addition, they also take advantage of protocol handlers (i.e., HTTP and MAPI); a gatherer to manage the URL queue; and filters that actually extract textual information from the source. Full-text searching is used in products other than Microsoft SQL Server (Microsoft Exchange Server, Microsoft SharePoint technologies, and the Indexing Service for Microsoft Windows Server), to find the following:
- Text data in text, ntext, char, nchar, varchar, and nvarchar columns
- Text data stored in a column with data type image
- Text data in Word documents, Web sites, Excel documents, text files, and PowerPoint presentations (these are native filters in SQL Server, although more can be added)
The MSSEARCH Service
In SQL Server 7.0 and 2000, Full-Text Searching and Indexing is handled by the MSSEARCH service. Only one instance of the MSSEARCH Service is installed and run on a server regardless of the number of instances of SQL Server installed. This means that the service is shared with all other components on the server that use the MSSearch service. It must run under the Local System context so that it can keep track of the MSSQLServer service account and handle modification of the full-text catalogs, since catalogs are stored outside of the database files. Once installed no modification can be made to the service, nor can changes be made to the MSSQLServer account except through the server properties of Enterprise Manager.
The MSFTESQL Service
The MSFTESQL Service (Microsoft Full-Text Engine for SQL Server) in SQL Server 2005 is a new and improved version based on the Microsoft Search Service. For each instance of SQL Server 2005 installed on the server there is an instance of the MSFTESQL service. This allows exclusive use of the service by a SQL Server instance. The pictures below show the running services in SQL Server Configuration Manager on different computers:
 |
 |
Unlike MSSEARCH, the MSFTESQL Service does not have to run under the Local System account context; it does, however, have to run under the same security account as the MSSQLServer service, since they work hand-in-hand and need to keep track of one another.
Next Steps
- If you are looking for a new means to search your data, consider researching Full Text Search to incorporate more than just a literal search or using a LIKE clause.
- Keep in mind that full text search products are also available for files such as Word, Excel, etc., so consider researching these technologies.
- Review information on Microsoft Search Service and Microsoft Full-Text Engine for SQL Server
- Download the sample AdventureWorks database and work with the full-text search concepts.
- Tune in to MSSQLTIPS for more information on querying using Full-Text Search
About the author
MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
