MSSQL 2005 Setting up full-text search
I am starting my foray into MSSQL2005 full text search. This is a new in-dev database, so I can secure it.
Let's say I have a "Form" table that has a one-to-many relationship to two other tables: "Answer" and "Comment". I want the user to be able to enter one search query and link all three tables using full text search. What's the best way? It seems I can create an indexed view to speed things up.
If I am using an indexed view (is this generally the best route?), How do I handle one-to-many relationships? Suppose all I really want to figure out is the ID of the Form table, which will contain any search results found in the Response / Comment child tables, so I can display the entire form containing the records located. Inner join in the view will result in multiple rows of the "main" form being returned — not very good for performance. How can I concatenate all child response / comment lines into one column for the view? So instead of ....
Form 1, Answer A, Comment A
Form 1, Answer B, Comment A
Form 1, Answer A, Comment B
Form 1, Answer B, Comment B
... it will be...
Form 1, Answer A Answer B, Comment A Comment B
Remember, all I care about is the ID of this main form line.
This seems logical to me, but I'm not sure what works best for full text search, so just curious if I'm approaching it correctly.
UPDATE: It looks like indexed views are pretty strict; no left joins, no subqueries, so I can go down the path of a triggered pivot table if it's not too cumbersome.
source to share
In SQL you have to use this rule: - start with the item you want to retrieve - add filters to restrict the set of items to retrieve
it sounds like no problem, but that's really all you have to do. Here you need form elements (strings), so you write:
SELECT ID
FROM Form
and then add filters to limit the overall set to the set of forms that interest you. You do this using IN predicates. As you say, the answer and comment have a 1: n relationship with the form, which means that the answer and comment have an FK for Form.ID. Therefore, we can add where:
WHERE ID IN
(SELECT FormID FROM Answer WHERE CONTAINS(FullTextColumnInAnswer, @stringToSearch))
OR
ID IN
(SELECT FormID FROM Comment WHERE CONTAINS(FullTextColumnInComment, @stringToSearch))
this will result in a set of Form.ID values that are different and that are form IDs that have at least one answer or comment that contains a search string in a field that is a full text search.
This can be slower in SqlServer 2000 than in 2005 because you are using multiple CONTAINS statements.
You can use CONTAINSTABLE () instead in your connection script, see the BOL examples, although I would first apply the IN clause setting to get things working.
source to share