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
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.