Full Text Search in SQL Server 2005 Part 1:

While you think about text searching, LIKE operator come in your mind as you can search text within column with LIKE operator very easily in Microsoft SQL Server 2005. Suppose you have employee table than you may use below query to find first name:

Use AdventureWorks

Select * from Employee where FName LIKE 'rite%'

GO

But what if you wish to search part of the word? You may go for following query.

Use AdventureWorks

Select * from Employee where FName LIKE '%rite%'

GO

Yes, you can use above given query if you wish to find any string data in column contained “rite” but it will terribly slow as Indexes are searchable from the beginning of the words. Searching string within string won’t use B-Tree structure of an index to perform fast index search rather it scan full table and will slow up your search.

You can get rid of above problem by using FTS (SQL 2005 Full Text Search) which is third generation search component. You can find this tool with WorkGroup, Standard and enterprise version of SQL Server 2005.

Microsoft named this service as MSFTESQL (Microsoft Full Text Engine for SQL) which is disabled by default. You have to enable it in order to use it. I will explain you the benefit of the same with example and how to enable it.

First of all let me tell you how you can enable MSFTESQL services.

-- Open Service area configuration
-- Go to Surface Area Configuration for services and configuration
-- You will find “Full-Text Search”, Click on “Start” if it is not started yet.

After running your MSFTESQL service, you are ready to use Full Text Search functionality of Microsoft SQL Server 2005. Once you enabled FTS, you will get advanced feature like wildcard search, search one word near another word, searching character data with embedded binary objects stored with SQL Server.

I will be writing series of articles for Full Text Catalog. My next article will cover creating and maintaining Full Text catalog.

Reference: Ritesh Shah