CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)

This is sixth 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 five articles 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)

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

CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5)

Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.

CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

--this T-SQL will return only key value and rank

SELECT

[KEY],

[RANK]

FROM CONTAINSTABLE

(

FTSTest,

TestDescription,

N'FORMSOF(INFLECTIONAL, volatiles)'

);

GO

--this is sample search of CONTAINSTABLE

--with FORMSOF clause which we used in CONTAINS also in previous article

Select t2.* FROM CONTAINSTABLE

(

FTSTest,

TestDescription,

N'FORMSOF(INFLECTIONAL, volatiles)'

) as t1 join FTSTest t2 on t1.[key]=t2.Id

GO

--there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE

--You can give weight of the search word between 0.0 to 1.0

--search word with greater weight comes up with higher rank so that you can sort it easily.

SELECT

ct.[RANK],

ct.[KEY],

pm.[TestName],

pm.[TestDescription]

FROM CONTAINSTABLE

(

ftsTest,

TestDescription,

N'ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))'

) ct

INNER JOIN FTSTest pm

ON ct.[KEY] = pm.ID

ORDER BY ct.[RANK] DESC;

GO

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