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:

Document TYPE COLUMN FileExtension LANGUAGE 1033
KEY INDEX PK_Document_DocumentNode
ON (AdvFTCatalog)

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 Protection Status

SQL Server 2008 Topics