Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

How can I create a Full Text Search catalog on 3 fields where one is in a second table?

share|improve this question

2 Answers

up vote 2 down vote accepted

You'd have to create a view and create an FT index on this view

From MSDN "Getting Started with Full-Text Search" (my bold)

Create a full-text index on the table or indexed view

I haven't tried this myself so don't have any tips. However, an indexed view is the only way to have any index span tables.

share|improve this answer
since 2 of my 3 fields are in a table can i add the third as a calculated field and have a FTS on the single table? I am trying to make a FTS on Message.Subject, Message.Body and MessageRecipient.FriendlyName – kacalapy May 10 '11 at 12:22
up vote 2 down vote

The only way to do this is to create an "indexed view" that combines the two tables into a view. You can then build the full text index on that.

Here is the documentation you need direct from Microsoft.

http://msdn.microsoft.com/en-us/library/ms187317(v=SQL.90).aspx

share|improve this answer
what about adding a calculated field to the table that would add the other table's data. sort of like a join? – kacalapy May 10 '11 at 12:44
1  
To use a calculated field that needs data from another source you'd have to build it into a function. You'd then reference the function in the calculated field. Once you did that I'm not sure the field would be available for full text indexing. I've frankly never tried it. Even if it did work I think building an indexed view would be cleaner and more performant. Above you say, "sort of like a join" - an indexed view actually is a join. Behind the scenes sql builds a little table from the join that it can index and search. – RThomas May 10 '11 at 15:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.