FT searching and weights

I have a couple questions about fulltext searching...
1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
catalog. I'm getting results, but I think the rankings should be different.
My lower rankings should be higher than the highest ranking results. Can I
give weight to specific columns with FULLTEXTTABLE? I don't see that option
available in BOL with FULLTEXTTABLE. If not, how could I skew the results by
saying one column is more important than another column?
2) Assume a scenario...
I index a table (10 columns, 10MB size, 100,000 rows)
Catalog is populated.
I empty and re-populate the table. Most of the data would be identical.
Can I get away with doing an incremental update? Would this be faster than a
re-population?
Or would it basically be the same thing as a full re-population?
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Hi Shank,
#1. You can do something like the below CONTAINSTABLE (or FREETEXTTABLE as
there is no FULLTEXTTABLE ) query below using the Northwind employee table
and it's two FT-enabled columns Notes and Title with different weights:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Also, you should review SQL Server 2000 BOL title "Full-text Search
Recommendations" and the last paragraph on RANK as in order to get valid
RANK values you must have statisticlly significatntly number of rows
(10,000+) in your FT-enable table.
#2. Since you're using SQL Server 2000, I'd strongly recommend that you
consider using "Change Tracking" and "Update Index in Background" as
Incremental Populations can and do take as long to complete as Full
Populations, even with no changes. If you're emptying the table and
repopulating it, I'd recommend a Full Population over CT with UIiB and over
Incremental Pop as the Incremental will have to first delete all the entries
in the FT Catalog and then re-populate it, so a Full Population is best
given your situation.
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:#jZ5A47fEHA.2524@.TK2MSFTNGP09.phx.gbl...
> I have a couple questions about fulltext searching...
> 1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
> catalog. I'm getting results, but I think the rankings should be
different.
> My lower rankings should be higher than the highest ranking results. Can I
> give weight to specific columns with FULLTEXTTABLE? I don't see that
option
> available in BOL with FULLTEXTTABLE. If not, how could I skew the results
by
> saying one column is more important than another column?
> 2) Assume a scenario...
> I index a table (10 columns, 10MB size, 100,000 rows)
> Catalog is populated.
> I empty and re-populate the table. Most of the data would be identical.
> Can I get away with doing an incremental update? Would this be faster than
a
> re-population?
> Or would it basically be the same thing as a full re-population?
>
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
>