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.
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