Re: FTI, Searching and other Filters

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!



.