Full-text indexing: Overview and Installation

By Bill Graziano on 27 June 2000 | Tags: Full-Text Indexing

This is the first in a series of articles covering full-text indexing. In this version we cover installation, index creation and a basic query.

Microsoft SQL Server 7.0 includes a feature called Full-Text Indexing. This includes the ability to perform complex queries against character data. These queries can include word or phrase searching, proximity searches, inflectional matches (drive = drove) and revelance ranking (how close are the words). Full-text indexing is installed as a separate feature through the install program. It can only be installed on the Server version of SQL Server.

It will install an additional service onto the server called Microsoft Search. This service updates the indexes and helps perform the queries. It will also add an item for each database in the Enterprise Manager called Full-Text Catalogs. This will appear after User Defined Datatypes. The first step is create a full-text catalog. This is an operating system file that can hold full-text indexes for a number of different tables in your database. In this case I'm creating an index called FTCatalog. Notice that there is also a tab for schedules.

This brings up an important point about full-text indexes. They do not dynamically update like regular indexes. You must run a special stored procedure to either incrementally update them or rebuild them. If you do an incremental update the table must have a timestamp field in the table. This dialog box allows you to create a job you an run automatically to rebuild or update the full-text indexes in this catalog. Once you have your catalog created we can add a table to be full-text indexed. You do this by right clicking on a table name and choose "Full-text Index Table." This will take you through a wizard with X basic steps.

First, you will select a unique index for the table. Full-text indexing must have a unique index on each table that is indexed.
Next you will select the columns that are indexed. In this case I'm indexing the Employees table in the Northwind database and I've chosen the fields LastName and Notes.
In the next screen you choose which full-text index to use. You are also given the choice to create a full-text catalog at this point if you hadn't already done so.
And last, you set the schedule for populating the index. If you have already created your catalog and set up a regular update schedule then that schedule will be displayed here.
Click on finish and your index is built for you.

Now we've created a catalog, indexed a table and populated the index so it's time to run a query. SQL Server uses two main functions, CONTAINS and FREETEXT to query a full-text index. The code to query the Notes field for any record containing the word 'college' looks like:

Select * from Employees where Contains(Notes, 'college')

As you can see this version of CONTAINS is a function. We could have replaced the where clause with Contains(*, 'college') and it would have searched all the full-text indexed fields in the table for 'college'. That's it for the first part of Full-text indexing. In future installments we'll cover relevance rankings, inflectional searches and programmatically updating the catalogs. And before you ask, SQLTeam.com doesn't support full-text indexing of our articles. Our hosting company hasn't installed full-text indexing on their SQL Server but I'm working on them. Continue on to Part 2.