FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4)

This is fourth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past three article in this series. Below are the links of the same.

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

 Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2)

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

BTW, I am going to use the same table I have created in my previous article so for practical exercise get the table and script from that article if you are directly landing to this page.

If you have read above articles than you must be clear with FREETEXT predicate. Now, we will continue over the topic of this article.

If you are SQL Server developer then you might know TVF. FREETEXTTABLE is working something like it. You have to use FREETEXTTABLE in FROM clause only, just like a regular table or TVF. It will accept three things as below.

1.)    Table Name

2.)    Column Name(s)

3.)    Search Text

It will not return any row with real value rather it will return two columns.

1.)    [KEY]

2.)    [RANK]

First column [KEY] will return the key value of the Index column you have in your original table so that you can join this [KEY] column to your original table and get real full row whenever it is needed.

Second column [RANK] will return relevance ranking of the search text in the column(s) you have specified in FREETEXTTABLE.

So now this is really enough theory, isn’t it? Let’s have some real practical task.

--as per explaination given above,

--below query will return just key and rank. you can confirm

--whether you get right key or not based on your search criteria

select [Key],[Rank] from FREETEXTTABLE(FTSTest,TestDescription,'Volatile')

GO

--just commpare [KEY] with ID column of our FTSTest

Select * from FTSTest

GO

--now we can get real value by making join of FREETEXTTABLE and FTSTest

SELECT T2.* FROM FREETEXTTABLE(FTSTest,TestDescription,'Volatile') as t1 JOIN FTSTest as T2

ON T1.[KEY]=T2.Id

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