Re: FTI, Searching and other Filters

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


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@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:OitVbpuHHHA.1468@xxxxxxxxxxxxxxxxxxxxxxx

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!



.

Relevant Pages

  • Re: FTI, Searching and other Filters
    ... Um, maybe I'm missing something here, but since you are using the rank ... If the content is editabel this is by far the more manageable, ... SQL Server MVPhttp://sqlblogcasts.com/blogs/simons ... Add a FTI to each of the Indexed Views. ...
    (microsoft.public.sqlserver.fulltext)
  • FTI, Searching and other Filters
    ... ASC ... Content contains the bits that make up either Word or RTF documents. ... We have FTI defined on Content / ContentType / DocumentID. ... Through our application we are limiting their searches to the top 1500 rank ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTI, Searching and other Filters
    ... We can't add any special tokens, the content is an image field. ... "SQL SERVER DBA"') ... Remove the FTI from the table. ... But what they would really like is the top 1500 rank for documents ...
    (microsoft.public.sqlserver.fulltext)
Loading