CODEWITHSUNDEEP
sqlsql-serversql-server-2008
inolen
inolen

Reputation: 607

Full-text search indexing only on certain rows

I have two tables, documents and revisions. Documents has two columns: document ID and title, and revisions has 3 columns: document ID, revision ID, body.

When I perform my full-text search against the body column of the revisions table, I'd only like to query against the latest revision. Is there a way to tell SQL Server to only index on rows for the latest revision of a document?

Upvotes: 2

Views: 156

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

Afaik, there is no way to index only records with a specific id, but you can add the latest revision id to your query.

That can be done like adding:

SELECT
  *
FROM
  revisions r
WHERE
  /* Your r.body condition AND */
  r.revisionId = 
    (SELECT
      MAX(mr.revisionId) 
    FROM
      revisions mr 
    WHERE mr.documentid = r.documentid)

If you always need only to index the latest version, it may be better to add the body of that revision to the documents table or to another table that has a 1:1 relation to document. That way, each document has one indexed revision, while the others are stored for backup only. This is specifically useful if your documents have many revisions.

Upvotes: 2

Related Questions