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?
Philip,
Since you have considered using an "Indexed View" as SQL Server 2005 can Full Text Index (FTI) indexed views (see "You can also create full-text indexes on indexed views." from the June CTP BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/1fa628ba-0ee4-4d8f-b086-c4e52962ca4a.htm - for reference) and are prevented from using IV's due to. Could you post more details on your tables,the joins, the UDF and your FTS query? Can the UDF be replaced with another view?
With more detail, perhaps a solution could be developed using CONTAINSTABLE or FREETEXTABLE...
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
No comments:
Post a Comment