CONTAINSTABLE in Full-text search:

Question

 

Hi,

I was looking CONTAINSTABLE in Full-text search. In the MSDN site (http://msdn.microsoft.com/en-us/library/ms189760%28v=sql.110%29.aspx) an example is as given below:

SELECT select_list
FROM table AS FT_TBL INNER JOIN
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY]

Can anybody please tell me what is [KEY] in the above query?

Thanks,

Santosh

Answer 1

Hi Santosh, CONTAINSTABLE and FREETEXTTABLE query the full text index for the search terms and return a table of results.  The resulting table contains two additional columns, "KEY" and "RANK".

Here is a nice blog writeup on this functionality:
http://www.sitepoint.com/sql-server-full-text-search-protips-part-3-getting-ranked/

And here is the definition of KEY and RANK from that post:
The table these functions return contains two columns:

1. KEY:  this contains the primary key of the row in the indexed table. Obviously, but  importantly, its data type is the same as the primary key in that table.
2. RANK:  this is a positive integer representing the relative strength of the match to  your search term. This number is not an absolute value—it does not start at some  fixed value and work down to near zero. The specific value is only relative  compared to other results from that Free-Text query. Comparing the RANK value  across different Full-Text queries does not necessarily get meaningful results.

Thanks,
Sam Lester (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.