We have a need for full-text search over related tables. I'll refer to these tables as A, B & C.
Table A is the base table and is optionally related to table B through an outer join. Table C optionally has keywords that describe table A and are coalesced in a view with table A using a function.
We are using SQL Server 2005 and would like to have a single full-text query search over A, B & C. I thought using an indexed view might be the answer, but we have been prevented from creating the clustered index on the view because of table B being an outer join and table C being joined via the function to coalesce its data.
What do you suggest we try?