If your database contains many columns that use string data types such as VARCHAR or NVARCHAR, you might find that searching these columns for data by using the Transact-SQL = and LIKE operators does not perform well. A more efficient way to search text data is to use the SQL Server FTS capabilities.
You might be a bit surprised to learn that SQL Server doesn’t handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a variety of applications, including SQL Server, Exchange, and SharePoint.
SQL Server uses an interface component, the SQL Server Handler, to communicate with the Microsoft Search Service. The Handler extracts data from SQL Server tables that have been enabled for full-text searching and passes it to the search service for indexing.
Let us identify different components of Full text search before going ahead with understanding the architecture.
· The Microsoft Full-Text Engine for SQL Server (MSFTESQL)
· The Microsoft Full-Text Engine Filter Daemon (MSFTEFD) that comprises the following:
1. Filter
2. Protocol handler
3. Word breaker
MSFTESQL is a windows service that is a tightly integrated component of SQL Server 2005 which is used as a search engine. This is built on the (MSSearch) technology. MSFTESQL service is mainly involved in providing three functionalities.
· Implementing full-text catalogs and indexes for database
· Querying the database with words, phrases and words in close proximity [meaning of a word]
· Managing the full-text catalogs that are stored in SQL servers
MSSearch is a process in windows system that is responsible for doing the cataloguing and indexing. This file name is mssearch.exe. This process will be used by MSFTESQL service to implement the indexing and full-text catalogs.
Let us discuss the steps for implementing full-text search in SQL Server 2005.
· Adding a full text search catalog to the database – To add a full text search catalog we need to open the database node in Management studio and click on the storage node. We can see the Full text catalog in this node. We have an option for adding the full-text catalog by which we can add a new catalog.
· Adding the Full text indexing table – We need to specify to the database that we want to implement the full-text search functionality for a table. To do this we can select the respective table and select the right click option of Full-Text Index and Define Full-Text index. This opens up the Full text indexing wizard.
1. Using the Full text indexing wizard we select a unique index.
2. Once we select a unique index and move to the next step, we select the columns that are eligible for full text queries. This enables the full-text search for the selected columns.
3. We need to select the option of how can we track the changes. If automatically selected then a full population of indexing occurs. To avoid a population at the end of this wizard, select the Do Not Track Changes option, and clear the Start Full Population When Index Is Created checkbox.
4. Once we are done with step 3 we need to map the catalog out of all the catalogs at the database level. Or we could even create a new catalog for the full text search.
5. This is the last step of the wizard. Here we can create a table schedule and catalog schedules and schedule them with time. A table schedule can have a name, time of schedule and occurrences.
Once we create them and click on the finish button the full-text index is created.
— I Followed these steps, but i founded some troubles by the fact that i wasn’t enabling the DB for full text search catalog, and i was forced to run a specific Stored Procedure.
here are some screen shots from those steps:

DB Text Catalog

Warning Message

Run The System SP

Enable the Table and Columns to be indexed
