CONTAINSTABLE Column Weighting in FTS 2005

CONTAINSTABLE Column Weighting in FTS 2005

Hi there,

Is it possible with SQL Server 2005 to add weighting to a column in a 
CONTAINSTABLE or FULLTEXTTABLE ?

I've got the statment :

SELECT ftt.RANK, [content].id, [content].title, [content].content_data FROM 
[content] INNER JOIN CONTAINSTABLE([content], (title, content_data), 
'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc

What I need to do is ensure that matches in the title column have much 
higher ranking than matches in content_data.

I've seen similar posts, but have not seen a definitive yes/no answer for 
2005.

Many thanks,
Larry.

date: Mon, 5 Mar 2007 15:39:56 -0000 author: Larry Neylon

Re: CONTAINSTABLE Column Weighting in FTS 2005

Hello Larry,

Do two searches, we also include the job title in the content data. This 
is to because the searhc has to match in at least on column, not across columns. 
i.e. a search for SQL AND server AND DBA with column title = "SQL DBA" and 
content of "SQL Server" would not return as result unless you combined the 
data. 

SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] 
left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON fttitle.[key]=[content].id 

INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON 
ftt.[key]=[content].id 
ORDER BY
fttitle.rank desc, ftt.rank desc


> 

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


> Hi there,
> 
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> 
> I've got the statment :
> 
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title,
> content_data), 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY
> ftt.rank desc
> 
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> 
> I've seen similar posts, but have not seen a definitive yes/no answer
> for 2005.
> 
> Many thanks,
> Larry.

date: Mon, 5 Mar 2007 17:19:27 +0000 (UTC) author: Simon Sabin ail

Re: CONTAINSTABLE Column Weighting in FTS 2005

Hi Simon,

Thanks for the reply.

Does having multiple joins have a big impact on performance ?

Would I need to have another join for every field I wanted to search on in 
the table ?

Is there anyway of combining the rankings ?

Many thanks,
Larry.

"Simon Sabin" <SimonSabin@noemail.noemail> wrote in message 
news:62959f1a50b1e8c92d61906228a8@msnews.microsoft.com...
> Hello Larry,
>
> Do two searches, we also include the job title in the content data. This 
> is to because the searhc has to match in at least on column, not across 
> columns. i.e. a search for SQL AND server AND DBA with column title = "SQL 
> DBA" and content of "SQL Server" would not return as result unless you 
> combined the data.
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
>> FROM [content]
> left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON 
> fttitle.[key]=[content].id
> INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON 
> ftt.[key]=[content].id ORDER BY
> fttitle.rank desc, ftt.rank desc
>
>
>>
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>> Hi there,
>>
>> Is it possible with SQL Server 2005 to add weighting to a column in a
>> CONTAINSTABLE or FULLTEXTTABLE ?
>>
>> I've got the statment :
>>
>> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
>> FROM [content] INNER JOIN CONTAINSTABLE([content], (title,
>> content_data), 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY
>> ftt.rank desc
>>
>> What I need to do is ensure that matches in the title column have much
>> higher ranking than matches in content_data.
>>
>> I've seen similar posts, but have not seen a definitive yes/no answer
>> for 2005.
>>
>> Many thanks,
>> Larry.
>
>

date: Mon, 05 Mar 2007 21:03:59 GMT author: Larry Neylon

Re: CONTAINSTABLE Column Weighting in FTS 2005

Here is an example:

SELECT [KEY], SUM(Rank) AS WeightedRank
    FROM
    (
        SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Address, 'Street')
        UNION
        select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Realtor, 'Street')
        UNION
        select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Notes, 'Street')
    ) as x
    GROUP BY [KEY]

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



"Larry Neylon"  wrote in message 
news:uFfhHyzXHHA.4692@TK2MSFTNGP04.phx.gbl...
> Hi there,
>
> Is it possible with SQL Server 2005 to add weighting to a column in a 
> CONTAINSTABLE or FULLTEXTTABLE ?
>
> I've got the statment :
>
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data 
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title, content_data), 
> 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
>
> What I need to do is ensure that matches in the title column have much 
> higher ranking than matches in content_data.
>
> I've seen similar posts, but have not seen a definitive yes/no answer for 
> 2005.
>
> Many thanks,
> Larry.
>

date: Tue, 6 Mar 2007 16:10:54 -0500 author: Hilary Cotter

Re: CONTAINSTABLE Column Weighting in FTS 2005

Hello Larry,

It depends on the searches and the data in the index. It can do.

But generally the job title will be small and so should be a quck search.

We combine the rankings by in my example doing fttitle.RANK * 100 + ftt.RANK. 
This means that a match in the job title will always rank above a match in 
the content. This is because rank goes from 0-100


Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


> Hi Simon,
> 
> Thanks for the reply.
> 
> Does having multiple joins have a big impact on performance ?
> 
> Would I need to have another join for every field I wanted to search
> on in the table ?
> 
> Is there anyway of combining the rankings ?
> 
> Many thanks,
> Larry.
> "Simon Sabin" <SimonSabin@noemail.noemail> wrote in message
> news:62959f1a50b1e8c92d61906228a8@msnews.microsoft.com...
> 
>> Hello Larry,
>> 
>> Do two searches, we also include the job title in the content data.
>> This
>> is to because the searhc has to match in at least on column, not
>> across
>> columns. i.e. a search for SQL AND server AND DBA with column title =
>> "SQL
>> DBA" and content of "SQL Server" would not return as result unless
>> you
>> combined the data.
>> SELECT ftt.RANK, [content].id, [content].title,
>> [content].content_data
>>> FROM [content]
>>> 
>> left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON
>> fttitle.[key]=[content].id
>> INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt
>> ON
>> ftt.[key]=[content].id ORDER BY
>> fttitle.rank desc, ftt.rank desc
>> Simon Sabin
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/simons
>>> Hi there,
>>> 
>>> Is it possible with SQL Server 2005 to add weighting to a column in
>>> a CONTAINSTABLE or FULLTEXTTABLE ?
>>> 
>>> I've got the statment :
>>> 
>>> SELECT ftt.RANK, [content].id, [content].title,
>>> [content].content_data FROM [content] INNER JOIN
>>> CONTAINSTABLE([content], (title, content_data), 'Summary') as ftt ON
>>> ftt.[key]=[content].id ORDER BY ftt.rank desc
>>> 
>>> What I need to do is ensure that matches in the title column have
>>> much higher ranking than matches in content_data.
>>> 
>>> I've seen similar posts, but have not seen a definitive yes/no
>>> answer for 2005.
>>> 
>>> Many thanks,
>>> Larry.

date: Mon, 4 Jun 2007 22:48:39 +0000 (UTC) author: Simon Sabin ail

Re: CONTAINSTABLE Column Weighting in FTS 2005

Thanks for that Hilary,

This appears to be pretty close to what I'm after.

The only question I now have is how best to return the actual data in one 
SQL statement

The following would give me what I need, but is this the most efficient way 
of achieving this ?

SELECT [KEY], SUM(RANK) AS WeightedRank, content.title, content.abstract 
FROM

(

SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(content, (title, 
keywords), 'Content')

UNION

select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(content,(abstract), 
'Content')

UNION

select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(content,(content_data), 
'Content')

) AS x INNER JOIN content ON content.id = [KEY]

GROUP BY [KEY], content.title, content.abstract

ORDER BY WeightedRank DESC

Many thanks,
Larry.

"Hilary Cotter"  wrote in message 
news:%23c$xVODYHHA.984@TK2MSFTNGP04.phx.gbl...
> Here is an example:
>
> SELECT [KEY], SUM(Rank) AS WeightedRank
>    FROM
>    (
>        SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Address, 'Street')
>        UNION
>        select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Realtor, 'Street')
>        UNION
>        select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Notes, 'Street')
>    ) as x
>    GROUP BY [KEY]
>
> -- 
> 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
>
>
>
> "Larry Neylon"  wrote in message 
> news:uFfhHyzXHHA.4692@TK2MSFTNGP04.phx.gbl...
>> Hi there,
>>
>> Is it possible with SQL Server 2005 to add weighting to a column in a 
>> CONTAINSTABLE or FULLTEXTTABLE ?
>>
>> I've got the statment :
>>
>> SELECT ftt.RANK, [content].id, [content].title, [content].content_data 
>> FROM [content] INNER JOIN CONTAINSTABLE([content], (title, content_data), 
>> 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
>>
>> What I need to do is ensure that matches in the title column have much 
>> higher ranking than matches in content_data.
>>
>> I've seen similar posts, but have not seen a definitive yes/no answer for 
>> 2005.
>>
>> Many thanks,
>> Larry.
>>
>
>

date: Wed, 07 Mar 2007 21:44:39 GMT author: Larry Neylon