FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3)

If you have landed directly to this article and wish to have detail information about iFTS, I would recommend you to look at my previous two articles, Part1 and Part2 which covered basic information about iFTS.

FREETEXT predicate plays very crucial role in iFTS search. It is a basic yet very useful mechanism. This predicate searches a word that matches with inflectional forms and thesaurus.  You can use FREETEXT predicate on one column or on list of column inside your Full Text Index.

We have already created Full Text Catalog and Full Text Index along with one sample table in my previous article. We are going to use that table and index herewith in this article.

--this search will be performed on every column

--which are the part of our Full Text Index

--in our case, we have two columns (TestName, TestDescription)

--in our Full Text Index.

Select * from FTSTest where FREETEXT(*,N'Volatile')

--following search will be performed on perticular columns only

Select * from FTSTest where FREETEXT(TestDescription,N'Volatile')

Select * from FTSTest where FREETEXT(TestName,N'Volatile')

--if you will try to use FREETEXT in the column which is

--not part of fullText Index, you will be greeted with error like belo.

--Msg 7601, Level 16, State 3, Line 1

--Cannot use a CONTAINS or FREETEXT predicate on column 'Id' because it is not full-text indexed.

Select * from FTSTest where FREETEXT(ID,N'Volatile')

This is first step into the world of iFTS. See you soon with some more articles in this series only.

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 of http://www.SQLHub.com