FTI, Searching and other Filters

We have this table...
CREATE TABLE [dbo].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[HumanResourceID] [int] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Content] [image] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
[Version] [timestamp] NOT NULL,
[EmployeeID] [int] NULL,
CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
(
[ResumeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Content contains the bits that make up either Word or RTF documents.
We have FTI defined on Content / ContentType / DocumentID. Generally FT
searches are working.
The table contains over 130k documents.
Through our application we are limiting their searches to the top 1500 rank
of any FT search. (So as not to over-burden our server.)
This works when the want to search the table for documents within the entire
company.
But what they would really like is the top 1500 rank for documents within
their office.
Is there a way to partition the the table by an OfficeID with some what to
pre-filter so the FT search is only looking at Documents from one or more
OfficeIDs?
TIA - Kyle!
I think I found my answer...although a lot of work.
Remove the FTI from the table.
Add an OfficeID column to the table and populate it.
Partition the table by the OfficeID column.
Create an Indexed View for each OfficeID
(Open a new office, the add a new OfficeID and a new Indexed View for that
OfficeID.)
(Close an existing Office, migrate the documents to a different office, drop
the FTI for that View and drop that View)
Add a FTI to each of the Indexed Views.
Mod the application so it knows what how to FT search one or more Indexed
Views and combine the results from multiple views if needed.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
> We have this table...
> CREATE TABLE [dbo].[Document](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [HumanResourceID] [int] NOT NULL,
> [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Content] [image] NOT NULL,
> [DateEntered] [datetime] NOT NULL,
> [DateModified] [datetime] NULL,
> [Version] [timestamp] NOT NULL,
> [EmployeeID] [int] NULL,
> CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
> (
> [ResumeID] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> Content contains the bits that make up either Word or RTF documents.
> We have FTI defined on Content / ContentType / DocumentID. Generally FT
> searches are working.
> The table contains over 130k documents.
> Through our application we are limiting their searches to the top 1500
> rank of any FT search. (So as not to over-burden our server.)
> This works when the want to search the table for documents within the
> entire company.
> But what they would really like is the top 1500 rank for documents within
> their office.
> Is there a way to partition the the table by an OfficeID with some what to
> pre-filter so the FT search is only looking at Documents from one or more
> OfficeIDs?
> TIA - Kyle!
>
|||Hello Kyle,
The other option is to add the office ID to the content (If the content was
editable i.e. text/html)
Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
SERVER DBA"')
If the content is editabel this is by far the more manageable, and scalable.
We did the index view thing and it is just not a neat solution. The token
thing is much easier.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> I think I found my answer...although a lot of work.
> Remove the FTI from the table.
> Add an OfficeID column to the table and populate it.
> Partition the table by the OfficeID column.
> Create an Indexed View for each OfficeID
> (Open a new office, the add a new OfficeID and a new Indexed View for
> that
> OfficeID.)
> (Close an existing Office, migrate the documents to a different
> office, drop
> the FTI for that View and drop that View)
> Add a FTI to each of the Indexed Views.
> Mod the application so it knows what how to FT search one or more
> Indexed Views and combine the results from multiple views if needed.
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in
> message news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
|||Um, maybe I'm missing something here, but since you are using the rank
I'm assuming you are using CONTAINSTABLE or FREETEXTTABLE and joining
back to Document. I'm also assuming you are using a "TOP 1500" and an
"ORDER BY Rank" in your query. Could you then just add "AND OfficeID =
'USEROFFICEID'" to your WHERE clause after adding the OfficeID field to
the table?
On Dec 14, 7:32 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:[vbcol=seagreen]
> Hello Kyle,
> The other option is to add the office ID to the content (If the content was
> editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
>
>
>
>
|||Looks like I'm stuck.
In order to create a Full Text Index on my view, the view has to have a
unique index.
SQL 2005 doesn't allow you to create a index on a view if the view contains
an text, ntext, image or xml columns.
And mine does because we're storint the resume in an image column and the
resume is what we're after.
We can't add any special tokens, the content is an image field.
Any other ideas?
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
> Hello Kyle,
> The other option is to add the office ID to the content (If the content
> was editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and
> scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Kyle,
a) you can create an index on a view if you use VARCHAR(MAX) or
VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql 2005).
b) AFAIK, those "tokens" can be other columns from the same view/table that
are also indexed in that FT catalog. You just specify CONTAINS(*,... Instead
of CONTAINS(MyClobColumn,...
it should work.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
> Looks like I'm stuck.
> In order to create a Full Text Index on my view, the view has to have a
> unique index.
> SQL 2005 doesn't allow you to create a index on a view if the view
> contains an text, ntext, image or xml columns.
> And mine does because we're storint the resume in an image column and the
> resume is what we're after.
> We can't add any special tokens, the content is an image field.
> Any other ideas?
>
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
>
|||I will have to try changing the type over to VARBINARY(MAX). That may work.
It mght also be easier to FTI a second column since that will require less
program changes.
Thanks
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added Office nvchar(8) to my table and populated int
select count(DocumentID) from Document where contains(*, 'Office01 and
cobol')
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added a Office nvchar(8) column and populated it.
No matter what I specify for a search condition, it doesn't return what I'm
after.
For a test I tried
select count(DocumentID) from Document where contains(*, 'Office02 and
cobol')
'Office02' is in the new column, 'cobol' is in the image column.
If I search for them seperately there is overlap so the data is correct.
Ideas?
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||To be clear the 'Office' column will only ever hold an office identifier
liket 'Office01' or 'Office13'.
The Content column has the interedting data.
We want to use containstable to give us the top n by rank DocumentIDs for a
particular office.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uM6mqsRKHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I added a Office nvchar(8) column and populated it.
> No matter what I specify for a search condition, it doesn't return what
> I'm after.
> For a test I tried
> select count(DocumentID) from Document where contains(*, 'Office02 and
> cobol')
> 'Office02' is in the new column, 'cobol' is in the image column.
> If I search for them seperately there is overlap so the data is correct.
> Ideas?
> "Lakusha" <Lakusha@.excite.com> wrote in message
> news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
>

FT Searching in SQL Server Database

We've got a small Windows SharePoint Services site that stores content
in a remote SQL Server Database. Over the last couple of weeks I've
been trying to get the Search Service working to no avail - no results
are being returned when a search is being performed.
I've posted this to the Windows Sharepoint Group, but I suspect the
problem may be with the SQL Server installation. Here's a summary of
the setup/things checked... any pointers would be appreciated!
1. I'm running SQL Server 2000 with SP3
2. The Full Text component of SQL Server is installed
3. The Microsoft Search Service is running and logged in using local
system admin account
4. There are no errors reported in the Event Log
A clean install of WSS was performed and the FT Search and Indexing
option for the site under SharePoint is all configured correctly.
When I look at the WSS Installation Guide it talks about an empty
index being created called ix_<databasename> when WSS is installed. I
have very little experience of SQL Server, but I did check this using
Enterprise Manager and nothing exists under the Full Text Catalogs
node. Is it possible to recreat this manually?
Thanks.Steve,
Windows SharePoint Services or WSS as it's sometimes called, can use SQL
Server's Full-Text Search (FTS) components. However, you need to do a couple
of things when you migrate or upgrade from WMSDE (a special Windows 2003
version of MSDE) to SQL Server. Specifically, for your named instance
"SHAREPOINT", when you upgraded to SQL Server, under Server Components, did
you elect the Full-text Search check box?
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Steve" <bennett_s_nospam@.yahoo.com> wrote in message
news:190ea2c6.0312031335.630fc2dc@.posting.google.com...
> We've got a small Windows SharePoint Services site that stores content
> in a remote SQL Server Database. Over the last couple of weeks I've
> been trying to get the Search Service working to no avail - no results
> are being returned when a search is being performed.
> I've posted this to the Windows Sharepoint Group, but I suspect the
> problem may be with the SQL Server installation. Here's a summary of
> the setup/things checked... any pointers would be appreciated!
> 1. I'm running SQL Server 2000 with SP3
> 2. The Full Text component of SQL Server is installed
> 3. The Microsoft Search Service is running and logged in using local
> system admin account
> 4. There are no errors reported in the Event Log
>
> A clean install of WSS was performed and the FT Search and Indexing
> option for the site under SharePoint is all configured correctly.
> When I look at the WSS Installation Guide it talks about an empty
> index being created called ix_<databasename> when WSS is installed. I
> have very little experience of SQL Server, but I did check this using
> Enterprise Manager and nothing exists under the Full Text Catalogs
> node. Is it possible to recreat this manually?
> Thanks.

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

FreeTextTable multiple Columns

I'm trying to do a FREETEXTTABLE search on two columns.
I can get it to work correctly with searching only one column.
I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.
What I have so far is:
SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c
I picture it being in the WHERE clause as:
WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))
but I haven't found an example where I can put it in the where clause.
Thanks for any help,
Tim
Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:
use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows
Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows
Hope this helps!
Regards,
John
"Tim" <tim_cavins@.hotmail.com> wrote in message
news:3020d2f4.0407071416.2866d84b@.posting.google.c om...
> I'm trying to do a FREETEXTTABLE search on two columns.
> I can get it to work correctly with searching only one column.
> I basically need it to do an OR on the two columns and haven't been
> able to find anything on that. It seems like I'm limited to finding
> matches that have it in both columns.
> What I have so far is:
> SELECT a.Name, b.rank, c.rank
> FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
> FreeTextTable(Test, SolutionProblem, 'author) AS c
> I picture it being in the WHERE clause as:
> WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
> SolutionProblem, 'author'))
> but I haven't found an example where I can put it in the where clause.
> Thanks for any help,
> Tim

Freetexttable

I am very new to the freetext searching and have not been
able to decipher some of the documentation steps in making
FreeTextTable work.
I have a table with a primary key of "KeyID", and my
search index is created on varchar fields of Problem and
Cause. I also have a field called WorkOrderID which is not
part of the index.
Basically I want to return the rank, the WorkOrderID, and
Problem/Cause if it matches. Pretty simple I should think.
I haven't found any line by line explanation of how the
query works.
It would be helpful if you could post the entire schema of this table. Here
is my stab in the dark as to what it would look like.
select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc
This will search for hits in any of the full text indexed columns, and will
search across columns. So if you are searching for James Bond, and one row
has the word James in the problem column and Bond in the Cause column this
will be a "hit"
If this won't work for you, you may have to do the more expensive:
select distinct KeyID, Problem, Cause, Rank=a.Rank +b.rank from authors,
FreeTextTable(TableName,Problem,'SearchPhrase') as a,
FreeTextTable(TableName,Cause,'SearchPhrase') as b where
a.[key]=KEYID or b.[key]=KEYID
order by Rank Desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:08f601c49cb2$87024080$a501280a@.phx.gbl...
> I am very new to the freetext searching and have not been
> able to decipher some of the documentation steps in making
> FreeTextTable work.
> I have a table with a primary key of "KeyID", and my
> search index is created on varchar fields of Problem and
> Cause. I also have a field called WorkOrderID which is not
> part of the index.
> Basically I want to return the rank, the WorkOrderID, and
> Problem/Cause if it matches. Pretty simple I should think.
> I haven't found any line by line explanation of how the
> query works.
>
|||I will try that also... table is:
KeyID - PK, int, identity
Problem - Varchar(2048)
Cause - varchar(2048)
WorkOrderID - int
All fields allow nulls except the PK of course.

FreeText on multiple columns

Hello!
I need to do a freetext-search on multiple columns in the same table,
searching with the same keywords.
For example
WHERE FREETEXT({col1, col2}, "searching for this")
I know that this can be accomplished through
WHERE FREETEXT(col1, "searching for this")
OR FREETEXT(col2, "searching for this")
but then it will query the FTS-database (in this case) two times. But
the more oclumns the more queryies against the FTS-database.
I tried to concatenate the columns, like
WHERE FREETEXT(col1+col2, "searching for this")
but that wont work.
I know that this is possible to do through contains/containstable, but I
need to use freetext this time.
Suggestions?
Thanks
I just figured out that since I only have one FT-catalog (which has all
the columns I want to query against) on the table, I can do
WHERE FREETEXT(*, "searching for this")
dotNet wrote:
> Hello!
> I need to do a freetext-search on multiple columns in the same table,
> searching with the same keywords.
> For example
> WHERE FREETEXT({col1, col2}, "searching for this")
> I know that this can be accomplished through
> WHERE FREETEXT(col1, "searching for this")
> OR FREETEXT(col2, "searching for this")
> but then it will query the FTS-database (in this case) two times. But
> the more oclumns the more queryies against the FTS-database.
> I tried to concatenate the columns, like
> WHERE FREETEXT(col1+col2, "searching for this")
> but that wont work.
> I know that this is possible to do through contains/containstable, but I
> need to use freetext this time.
> Suggestions?
> Thanks
|||This is correct. With FreeText and FreeTextTable if you issue queries where
you don't qualify the column name(s) and your search phrase consists of more
than 1 token, ie "searching for this" has three tokens or words, your search
results might come from different columns. IE Searching in col1, for in
col2, and this in col3. Keep in mind that for, and this are noise words.
With a Contains and ContainsTable searches, all the tokens/words will have
to be in the same column for you to get a hit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"dotNet" <dotnet@.brimba.nu> wrote in message
news:u0MXYc5AFHA.2608@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I just figured out that since I only have one FT-catalog (which has all
> the columns I want to query against) on the table, I can do
> WHERE FREETEXT(*, "searching for this")
>
>
> dotNet wrote: