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

Saturday, October 17, 2009 |

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.

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')

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

--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
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of