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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: