Fulltext index for 2 columns in one table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lekshmi - 22 Dec 2005 12:55 GMT

Hi All,

I have a table and a full-text catalog. The table is assigned to the catalog
and index on description. Now I have to add one more column to this existing
table called 'tilldescription'. The table will have 6-7 lakhs of data and I
have to search using this tilldescription. I thought of providing one more
ful-text index? Is it possible to assign the table to two full-text index
catalogs? Can I give both the columns to be indexed in the same catalog? What
happens if I index both on description and tilldescription in the same
full-text index catalog? What is the best solution? Please reply as son as
posible.

Thanks,
Lekshmi

Reply to this Message

Steve W - 22 Dec 2005 16:33 GMT

You can put both columns in one catalog (in fact I think you have to).

In Sql Server 2000 you can query one column or all columns. In Sql Server
2005 you can specify which columns to query.

Steve

> Hi All,
>
[quoted text clipped - 10 lines]
> Thanks,
> Lekshmi

Reply to this Message

Lekshmi - 26 Dec 2005 07:21 GMT

In one examples, I have seen that, to create an index on column A, this
column is copied to column B and the indexing is done on B? Is there any
specific reason for this?

Can you please explain me this line? "In Sql Server 2000 you can query one
column or all columns''?

Lekshmi

> You can put both columns in one catalog (in fact I think you have to).
>
[quoted text clipped - 21 lines]
> > Thanks,
> > Lekshmi

Reply to this Message

Daniel Crichton - 30 Dec 2005 09:26 GMT

Lekshmi wrote on Sun, 25 Dec 2005 23:21:02 -0800:

> In one examples, I have seen that, to create an index on column A, this
> column is copied to column B and the indexing is done on B? Is there any
> specific reason for this?

I'm assuming these examples are actually modifying the data in some way,
otherwise there is no reason to do this. For example, I have a column on my
table that I wanted to full text index that includes terms like C++, .Net,
and others that would be "broken" by the workbreakers. So I have a second
column, into which I write versions of the strings to be indexed - eg.
"Visual Basic.Net Developer's Guide" would be written to the second column
as "Visual BasicDOTNet Developer's Guide", and then when I need to search
for "Visual Basic.Net" I use "Visual BasicDOTNet" in my query.

> Can you please explain me this line? "In Sql Server 2000 you can query
> one column or all columns''?

You can do either

SELECT * FROM Table WHERE CONTAINS(*,"word")

or

SELECT * FROM Table WHERE CONTAINS(Col,"word")

where * is all indexed columns on the table, and Col is a single indexed
column.

In SQL Server 2005 you can now pass a list of indexed columns instead, eg.
if you have FTS indexes on Col1, Col2, and Col3, and you only want to search
for word in Col2 and Col3,

SELECT * FROM Table WHERE CONTAINS((Col2,Col3),"word")

Hope that helps to explain it.

Dan

>> You can put both columns in one catalog (in fact I think you have to).
>>
[quoted text clipped - 23 lines]
>>> Thanks,
>>> Lekshmi

Reply to this Message