Introduction of iFTS (Integrated Full Text Search – iFTS in SQL Server 2008 Part 1)

Full Text Search is not a new concept in SQL Server 2008. It has just some advanced feature and better architecture and that is why it is known as iFTS rather than FTS in SQL Server 2008.

FTS is nothing but just one of the powerful tool in SQL Server. SQL Server is a set based technology and very well known for its capability to handle big amount of data. It has powerful capacity for different data related operation like Insert, Update, Delete and Select. FTS is used for last operation “SELECT” most of the time.

Well after reading above two paragraphs, you might think that why we need FTS or iFTS since we are already having enough stuff to use in WHERE clause. But if you will practically see, what do you if you want to search column which has certain words inside it? Of course you will go for LIKE operator. Isn’t it?

Select * from emps where FullName LIKE '%Ritesh%'

If you will look at execution plan of above query, you will definitely see full table scan, even if you will have index on FullName column of emps table, because regular index works on predicate bases. 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.

So, this is all we have talked about FTS, what iFTS means? Is it something new creature in the world of SQL Server? No, my dear friend, it is new wine with old bottle I can say.

In iFTS (Integrated Full Text Search), Microsoft has tried to improve some old limitations. I am listing some of them below.

à Full Text Search is now a part of SQL Server Query Engine itself. Before It was separate service called MSFTESQL (Microsoft Full Text Engine for SQL), it was creating bit overhead to communicate with each other which is eliminated as now FTS is a part of main SQL Server Query engine.

àNoise word of FTS is now come up with new terminology “Stop List” in SQL Server 2008. It is not being stored in separate text file rather it stored in database itself.

àIn SQL Server 2008, you can change stop list and Thesaurus list before even restarting SQL Server Services.

So, these are few of the benefit and change you will get in iFTS over FTS. I guess you guys are now prepared to learn iFTS. Let us start our journey from next article.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com