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

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?
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> 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?
>
|||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?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
> 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.
|||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/
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> 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?
>
|||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?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uKI3DMLKFHA.2628@.tk2msftngp13.phx.gbl...
> 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/
>
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> statement,
>
|||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
"Denis" <denis@.pharmiweb.com> wrote in message
news:eIOgqtKKFHA.1500@.TK2MSFTNGP09.phx.gbl...
> 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?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
>
|||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.
|||SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.
|||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,fldRequirements htm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
> 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.
>
|||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.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e%23msOZZKFHA.656@.TK2MSFTNGP14.phx.gbl...
> 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,fldRequirements htm,'test')
> union
> select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
> 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
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
>