sql server full text search: I have a FT catalog with "product_name" and "product_description" fields in
it. If I do a containstable search using "*" to select all the fields, my
weighted results have weightings on "product_description" higher than on
"product_name", whereas I want to have hits on "product_name" always highest
in ranking.

How should I structure a query where I want to search both fields but want
to weight the results for "product_name" heavier than the results for
"production_description"?

Here is an example of what you are trying to do.

create database realtor
go
use realtor
GO
sp_fulltext_database 'enable'
GO
Create fulltext catalog realtor as default
GO
create table Listing(idListing int not null identity constraint ListingPK
primary key,
Address varchar(200), Realtor varchar(200), Notes varchar(200))
GO
insert into Listing(Address, Realtor, Notes)
values('123 Any Street','John Street','the word on the street is good')
insert into Listing(Address, Realtor, Notes)
values('123 Any Road','John Street','the word of mouth is good')
insert into Listing(Address, Realtor, Notes)
values('123 Any Road','John Smith','the word on the street is good')
insert into Listing(Address, Realtor, Notes)
values('123 Any Street','John Smith','the word of mouth is good')
insert into Listing(Address, Realtor, Notes)
values('Nothing','Nothing','Nothing')

GO
create fulltext index on listing(Address, Realtor, Notes) key index
ListingPK
GO
declare @getdate datetime
set @getdate=getdate()
select top 100 Address, Realtor, Notes,
RankTotal=isnull(RankAddress,0)+isnull(RankRealtor,0)+isnull(RankNotes,0)
from listing
left join (SELECT Rank * 5.0 as RankAddress, [KEY] from
FREETEXTTABLE(listing,
Address, 'Street')) as k on k.[key]=Listing.idListing
left join (select Rank * 3.0 as RankRealtor, [KEY] from
FREETEXTTABLE(listing,
Realtor, 'Street')) as l on l.[key]=Listing.idListing
left join (select Rank * 1.0 as RankNotes, [KEY] from FREETEXTTABLE(listing,
Notes, 'Street')) as m on m.[key]=Listing.idListing
where rankaddress is not null or rankrealtor is not null or ranknotes is not
null
ORDER BY RankTotal DESC
print datediff(ms, @getdate,getdate())
GO

declare @getdate datetime
set @getdate=getdate()


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



[quoted text, click to view]