sql server full text search: doing a FREETEXTTABLE on more than one columns -OR- creating a SECOND FT INDEX for a table -- sql server full text search

You're in the

sql server full text search

group:

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

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

John Kane
3/14/2005 8:29:40 AM

sql server full text search: 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
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

[quoted text, click to view]

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

Hilary Cotter
3/14/2005 10:20:19 AM

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.

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

[quoted text, click to view]

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

Hilary Cotter
3/14/2005 12:30:38 PM

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.

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

[quoted text, click to view]

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

Denis
3/14/2005 12:43:49 PM

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?

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

Denis
3/14/2005 3:35:08 PM

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?

[quoted text, click to view]

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

Denis
3/14/2005 4:44:54 PM

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?

[quoted text, click to view]

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

Denis
3/15/2005 9:53:00 AM

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.

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

Denis
3/15/2005 9:54:29 AM

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

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

Hilary Cotter
3/15/2005 2:36:36 PM

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

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

[quoted text, click to view]

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

Denis
3/16/2005 9:03:18 AM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server full text search group.

View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn