doing a FREETEXTTABLE on more than one columns -OR- creating a SECOND FT INDEX for a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Denis - 14 Mar 2005 13:43 GMT

Is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.

Alternatively, is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.

Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?

Reply to this Message

Hilary Cotter - 14 Mar 2005 16:20 GMT

SQL 2005 allows you to select which column you want returned in a full-text
search - this is for both Contains and FreeText. SQL FTS also allows you to
full-test index indexed views, which might also help you.

You can only create a single Full-Text index per table. I think what you
might have to do in SQL 2000 is to partition your table into multiple child
tables containing the columns you wish and then full-text index these
tables.

Signature

Hilary Cotter
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

> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
[quoted text clipped - 5 lines]
> Note: Full-Text seach MUST be used. I know I could write an OR statement,
> but it must be done with a FT search. Any ideas?

Reply to this Message

Denis - 14 Mar 2005 16:35 GMT

SQL2000 also lets you select a column, but only one! Does 2005 allow
multiple columns?

Partition the table to smaller ones? How do you mean that exactly?

> SQL 2005 allows you to select which column you want returned in a
> full-text
[quoted text clipped - 7 lines]
> tables containing the columns you wish and then full-text index these
> tables.

Reply to this Message

Hilary Cotter - 14 Mar 2005 18:30 GMT

No SQL 2000 allows you to search one column or all columns. SQL 2005 lets
you search one column, all columns, or any combination of columns.

To partition a table you have to make one or more tables with the same PK.

So if you have a table like this:

Create Table parent
( pk int not null identity constraint primarykey primary key,
charcol1 char(20),
charcol2 char(20),
charcol3 char(20),
charcol4 char(20))

You could create the following child tables

Create Table child1
( pk int not null identity constraint primarykey1 primary key,
charcol1 char(20))

Create Table child2
( pk int not null identity constraint primarykey2 primary key,
charcol2 char(20))

Create Table child3
( pk int not null identity constraint primarykey3 primary key,
charcol3 char(20))

And then use triggers or replication to keep them in sync.

Signature

Hilary Cotter
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

> SQL2000 also lets you select a column, but only one! Does 2005 allow
> multiple columns?
[quoted text clipped - 12 lines]
> > tables containing the columns you wish and then full-text index these
> > tables.

Reply to this Message

Denis - 15 Mar 2005 10:53 GMT

I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.

-- Old Search
--SET @dynQuery = @dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @RankField = 'KW.RANK'

-- NEW SEARCH
SET @dynQuery = @dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @RankField = 'KW.RANK'

SET @dynQuery = @dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @RankField = 'KWw.RANK'

SET @dynQuery = @dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @RankField = 'KWww.RANK'

I want my new search to look at Title, Description and Company name only,
rather than every field that was included And if EITHER of the fields
contain the search string i want them displayed.

Reply to this Message

Hilary Cotter - 15 Mar 2005 20:36 GMT

Create Table tblJobsDataWareHouse
(uid int not null identity constraint primarykey4 primary key,
fldRequirementshtm char(20),
fldJobTitle char(20),
fldCompanyName char(20))

insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values ('test',null,null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL,'test',null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'test')
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'rest')
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'test','create'
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
GO
sp_fulltext_catalog 'test','start_full'
go
select * from tblJobsDataWareHouse where contains(*,'test')
go
select * from tblJobsDataWareHouse as tbl join (
select * from containstable(tblJobsDataWareHouse,fldRequirementshtm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'test')
union
select * from containstable(tblJobsDataWareHouse,fldCompanyName,'test')
)as ft
on ft.[key]=tbl.uid

Signature

Hilary Cotter
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

> I looked at what you said but I am not sure how that would work. Let me
> give you an idea of the type of how i am trying to do the search.
[quoted text clipped - 24 lines]
> rather than every field that was included And if EITHER of the fields
> contain the search string i want them displayed.

Reply to this Message

Denis - 16 Mar 2005 10:03 GMT

many thanks for your code example.

After some thinking i decided that due to the structure of the database is
best not to break-down the table into smaller ones, so i guess i will have
to find another way of doing it.

But thank ytou very much for the time you took to answer my question.
again, many thanks.

> Create Table tblJobsDataWareHouse
> (uid int not null identity constraint primarykey4 primary key,
[quoted text clipped - 74 lines]
>> rather than every field that was included And if EITHER of the fields
>> contain the search string i want them displayed.

Reply to this Message

jonathan_lee - 27 Dec 2008 19:19 GMT

Thanks for the example. I used your sample; however, I am getting duplicates
(or more) for the same index.

using fts in multiple columns (columnA, columnB, columnC, columnD)
case1: if search found in columnA and columnB, result is 2 records with same
index.
case2: if search found in columnA,d columnB and columnC, result is 3 records
with same index.

Question: How do I uniquely show only one record even if search is found in
multiple columns using your sample below?

rgds,

Jonathan

>Create Table tblJobsDataWareHouse
>(uid int not null identity constraint primarykey4 primary key,
[quoted text clipped - 47 lines]
>> rather than every field that was included And if EITHER of the fields
>> contain the search string i want them displayed.

Reply to this Message

Denis - 15 Mar 2005 10:54 GMT

SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.

Reply to this Message

John Kane - 14 Mar 2005 17:29 GMT

Denis,
Yes, this can be done, see "SQL Server FTS across multiple tables or
columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
and substitute freetexttable for containstable in the examples.

Thanks,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

Reply to this Message

Denis - 14 Mar 2005 17:44 GMT

I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.

-- Old Search
--SET @dynQuery = @dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @RankField = 'KW.RANK'

-- NEW SEARCH
SET @dynQuery = @dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @RankField = 'KW.RANK'

SET @dynQuery = @dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @RankField = 'KWw.RANK'

SET @dynQuery = @dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @RankField = 'KWww.RANK'

I want my new search to look at Title, Description and Company name only,
rather than every field that was included in the index. Any ideas?

Or perhaps you could show me how CONTAINSTABLE could be applied in this
case?

> Denis,
> Yes, this can be done, see "SQL Server FTS across multiple tables or
[quoted text clipped - 15 lines]
> statement,
>> but it must be done with a FT search. Any ideas?

Reply to this Message