Searching Documents - SQL Server 2008

In addition to searching textual data stored in text-based columns of a database, iFTS can be used to search documents stored in the database. As with database searches, you need a full-text index for the document you want to search.

Creating a Full-Text Index for Documents

You can create a full-text index for a document using a variation of the CREATE FULLTEXT INDEX statement. A statement that creates a full-text index for the Production. Document table follows:

CREATE FULLTEXT INDEX ON Production.Document
(
Document TYPE COLUMN FileExtension LANGUAGE 1033
)
KEY INDEX PK_Document_DocumentNode
ON (AdvFTCatalog)
WITH
(
CHANGE_TRACKING AUTO,
STOPLIST = SYSTEM
);
GO

This version is very similar to the CREATE FULLTEXT INDEX statement for a database, with one slight difference. In this variation, the varbinary(max) Document column is specified as the column to include in the full-text index. Immediately following the column name is the TYPE COLUMN clause, followed by a column containing the document type. The FileExtension column is specified in this instance. This column contains document types of .doc, which indicates aMicrosoft Word document. The type column is particularly important, since it tells SQL Server which filter should be used to extract indexable information from the document.

You can query the sys.fulltext_document_types catalog view to get a list of all the supported document types and their associated extensions.

Querying Documents

The AdventureWorks sample database has a table called Production.Document, which contains Microsoft Word documents in a varbinary(max) column named Document. The AdventureWorks database comes with a full-text index already created on the Document column of the Production.Document table, so we can skip the full-text index creation statement for this example. The following sample FREETEXT query locates documents that have the word safe in them. It returns the two documents shown in Figure.

SELECT DocumentNode, Title, DocumentSummary
FROM Production.Document
WHERE FREETEXT (*, N'safe');

Results of the full-text document query

Results of the full-text document query


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics