Is this possible with Full Text Indexing?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Peterson - 19 Oct 2006 03:17 GMT

(SQL Server 2000, SP4)

Hello all!

I am wrestling with a problem that I hope someone can help me with. I have
a series of related tables (fairly normalized), each of which has a textual
column whose data will, for the most part, be unique from the other tables.
We have created a Full Text Catalog over these tables that will index these
text columns.

We are trying to write an interface to search over the entire "suite" of
tables, using an expression like "A and B" or "A or B". The problem arises
when we try and craft the appropriate query using the Full Text CONTAINS
predicate.

For example, lets say that we have Shape, Color, and Size tables. If
someone tries to search for "Circle or Red", I can pretty easily find the
appropriate records from each of the tables. However, if I try and search
for "Circle and Red", I'm having a more difficult time expressing the query
to restrict the results to only those records that match.

It's almost like I need a Full Text Catalog over the *entire* set of tables.
Working with the individual tables is giving me fits.

Any help/advice would be *much* appreciated. Thanks!

John Peterson

Reply to this Message

Hilary Cotter - 19 Oct 2006 16:26 GMT

In SQL 2005 you can create an indexed view over all tables and the full text
index the view. In SQL 2000 I think you best bet would be to create a table
which would contain the rows from all tables, full-text index it, and then
search it.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> (SQL Server 2000, SP4)
>
[quoted text clipped - 23 lines]
>
> John Peterson

Reply to this Message

John Peterson - 19 Oct 2006 20:33 GMT

Dear Hilary,

Thank you for the information! I didn't realize that SQL 2005 had that
capability (to full-text an Indexed VIEW) -- that's really neat! I'll have
to look into that a bit more...

Yeah, if we stick with SQL 2000, our thought was to build a side table as
you suggest.

Thanks again!

John Peterson

> In SQL 2005 you can create an indexed view over all tables and the full
> text index the view. In SQL 2000 I think you best bet would be to create a
[quoted text clipped - 28 lines]
>>
>> John Peterson

Reply to this Message