Can SQL Server Full Text Search include related tables?

Question

Hi all;

I need to do a Full Text Search where the search is applied across multiple columns where the text from all those columns are treated as a single (very long) varchar to filter against and find matches. Full Text Search appears to handle that.

But I also have child elements (1:many) from another table where one of the columns in those related rows need to be included in the search. And parent elements (many:1) where again a column in that related row needs to be included.

Is there a way to do this with Full Text Search?

And in the alternative, I thought of writing a very complex join that does return all the associated data together to search against. But... correct me if I'm wrong, that would be no more efficient than just reading it all into memory to search. Because it has to combine everything before performing the filter and therefore would need to read&combine every row of data.

And if there's another approach that would work, very happy to hear any suggestions. Because at present I think my best bet is to create a table that has a FK to the matching row and a varchar column that has all the text from all the table.columns concatenated and I Full Text Search that. This would be expensive because on every upsert of any matching row of data, I have to update this table.

thanks - dave

Answer 1

With both CONTAINS and FREETEXT, you can specify a list of column to search or an asterisk to search all fulltext-indexed columns. For instance:

SELECT - FROM tbl WHERE CONTAINS((a, b, c), 'searchstring')
SELECT - FROM tbl WHERE CONTAINS(*, 'searchstring')

The Docs says for CONTAINS that all columns must be the same table. This restriction is not given for FREETEXT, but I would not be surprised if it applies to FREETEXT as well, since it's kind of mind-boggling of what would happen if multiple tables were searched by the same predicate.

Answer 2

those related rows need to be included in the search.

In the search as it? Then you have to create a full text index on the related tables/columns as well.

Or in the result set? Then JOIN the tables, as usually.