Topics in this Tutorial

• Full-text searching
• Creating a full-text Catalog
• Populating a catalog
• Querying data with full-text search functions

Full Text Searching

Indexing large text fields externally

One of the new features introduced in Microsoft SQL Server 7.0 was the ability to use the Internet Information Server's feature of Full-Text Searching. This uses the MSSearch service to query text data in more sophisticated ways than is offered by the WHERE clause LIKE. It is also much faster on large tables and large text fields.

Microsoft SQL Server 2000 Full-text search now includes change tracking and image filtering.

Change tracking

This maintains a log of all changes to the full-text indexed data. You can update the full-text index with these changes by flushing the log manually, on a schedule, or as they occur, using the background update index option.

Image filtering

Image filtering allows you to index and query documents stored in image columns. The user provides the document type in a column that contains the file name extension that the document would have had if it were stored as a file in the file system. Using this information, full-text search is able to load the appropriate document filter to extract textual information for indexing. For more information, see Microsoft Search Service.

Create a Full-Text Catalog

Full-Text Catalogs

The Full-Text search capability in SQL Server actually uses external indexes. This is because Text, nText and image data types cannot be indexed in SQL Server, and even large Varchar, Char, nVarchar or nChar text fields would be poor candidates for standard clustered/nonclustered indexes.

Remember too that queries such as this:

Select * from countries
Where notes like '%beach%'

... would never invoke the use of an index in any case.

The full-text search system uses an external index of keywords and phrases that are linked back to the primary key of the record containing the data. The basic concept is the same as a web search engine. 'Noise' words such as 'the', 'a', 'or' are ignored. You can choose from several popular languages.

This diagram, taken from the online books, illustrates the idea very clearly:



The Full-Text Indexing Wizard

Once Full-text indexing has been installed you can create a catalog for any database you like with textual data.

This has a wizard interface and takes you through the catalog building in several easy steps.

It is invoked from the Tools menu.

• In Enterprise Manager select the database on which you want to build the catalog
• From the Tools menu, select Full-Text Indexing

• The wizard loads in its usual glitzy way