FTI, Searching and other Filters

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Sorting by first letter, but not really
    ... Smith ABC 1 ... ORDER BY rank ASC, lastName DESC ... FROM tableName AS a INNER JOIN tableName AS b ...
    (microsoft.public.access.queries)
  • 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)
  • Re: FTI, Searching and other Filters
    ... If the content is editabel this is by far the more manageable, ... Remove the FTI from the table. ... Partition the table by the OfficeID column. ... But what they would really like is the top 1500 rank for documents ...
    (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)