fastest way to search text value for words in SQL Server 2008

Question

I have a requirement to identify keywords stored in a table column in text passed to a stord procedure, at present I'm pasing the text in as rows in a table value, however as populating the table value is hideously slow, I'm going to scrap this approach. I looked at the full text search option, however based on my very limited understanding of this, specifically:-

1. The data has to already reside in a database table column.

2. I cannot see any obvious way of using freetext or contains such that the words / expressions you are searching for can be passed into this en mass from another database table.

Can someone please give me some ideas and suggestions here.

Answer 1

You can do en masse searches against existing full text indexed columns in SQL 2008 by joining onto the sys.dm_fts_index_keywords and sys.dm_fts_index_keywords_by_document table valued functions

For transient data you could look into using the sys.dm_fts_parser. Example usage in my answer to a similar question.