Friday, January 27, 2012 9:26 AM
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?
Monday, February 13, 2012 11:16 PM
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:
And here is the definition of KEY and RANK from that post:
The table these functions return contains two columns:
- 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.
- 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.
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.
- Proposed As Answer by Samuel Lester - MSFTMicrosoft Employee Thursday, February 16, 2012 10:47 PM