Full-text Indexes

Full-text support for Microsoft® SQL Server™ version 7.0 data involves two features: the ability to issue queries against character data, and the creation and maintenance of the underlying indexes that facilitate these queries. Full-text indexes and the tasks involved in creating and maintaining them are described here.

Full-text indexes differ from regular SQL indexes in a number of ways.

Regular SQL indexes	Full-text indexes
Stored under the control of the database in which they are defined.	Stored in the file system, but administered through the database.
Several regular indexes per table allowed.	Only one full-text index per table allowed.
Automatically updated when the data upon which they are based is inserted, updated, or deleted.	Addition of data to full-text indexes, called population, must be requested through either a schedule or a specific request.
Not grouped.	Grouped within the same database into a full-text catalog.
Created and dropped using Transact-SQL statements.	Created, managed, and dropped using stored procedures.

These differences make a number of administrative tasks necessary. Full-text administration is carried out at several levels:

Server
Certain server-wide properties, such as resource_usage, can be set to increase and reduce the amount of system resources used by the full-text service, Microsoft Search.

Note The full-text engine runs as a service named Microsoft Search on Microsoft Windows NT® Server. The Microsoft Search service is not available for installation with a Desktop SQL Server; rather, it is installed when the Full-Text Search feature is selected during a custom installation on Windows NT Server. While this means that the Microsoft Search service is not installed on either Microsoft Windows® 95/98 or Windows NT Workstation clients, these clients can make use of the service when connected to a SQL Server Standard or Enterprise installation. Currently, the SQL Server 7.0 Full-Text Search support cannot be used in a Windows NT Server, Enterprise Edition clustering environment.
Database
A database must be enabled to use the full-text service. Metadata for one or more full-text catalogs can be created and dropped in an enabled database.
Full-text catalog
A full-text catalog contains full-text indexes in a database. The catalog is populated with indexes using the administrative facilities described here. (Full-text catalogs must reside on a local hard drive associated with the machine on which SQL Server is running. Removable drives, floppy disks, and network drives are not supported.)
Table
A table must be enabled for full-text support. At that time, metadata, such as the name of the table and its full-text catalog, is created for the full-text index associated with the table. Once the table is enabled, you can populate it with the data in columns enabled for full-text support. If the full-text definition for a table is changed (for example, by including a new column that will also be indexed for a full-text search), the associated full-text catalog must be repopulated to synchronize the full-text index with the new full-text definition.
Column
Columns that support full-text queries can be added or dropped from an inactive registered table.

At all these levels, facilities are available to retrieve metadata and status information.

Unlike regular SQL indexes, full-text indexes are not automatically kept up-to-date as data is modified in the associated tables. Tables that can be updated should have their full-text indexes repopulated at appropriate intervals. This repopulation can be time-consuming and resource intensive; therefore, it is an asynchronous process that is usually run in the background during periods of low database activity. Tables with the same update characteristics (such as small number of changes versus large number of changes; or tables that change frequently during a particular time of day) should be grouped together and assigned to the same full-text catalog. This allows full-text catalog population schedules to be set up in a manner that will allow the full-text indexes to stay synchronous with the tables without adversely affecting the resource usage of the database server during periods of high database activity.

It is important to plan the placement of full-text indexes for tables in full-text catalogs. When you assign a table to a full-text catalog, consider the following guidelines:

Always select the smallest unique index available for your full-text unique key (a four-byte, integer-based index is optimal). Doing so significantly reduces the resources required by Microsoft Search service in the file system. If the primary key is large (over 100 bytes), consider choosing another unique index in the table (or creating another unique index) as the full-text unique key. Otherwise, if the full-text unique key size approaches the maximum size allowed (450 bytes), full-text population may not be able to proceed.
If you are indexing a table that has millions of rows, assign the table to its own full-text catalog.
Consider the amount of change occurring in the tables being full-text indexed, as well as the number of table rows. If the total number of rows being changed, together with the numbers of rows in the table present during the last full-text population, represents millions of rows, assign the table to its own full-text catalog.

There are two ways to administer full-text features: through a set of stored procedures and scalar functions, and through SQL Server Enterprise Manager.

The first method, through stored procedures and scalar functions, is described in the following topics: Scenario A: Enabling Others to Issue Full-text Queries; Scenario B: Performing Investigation and Clean-up Tasks for Full-text Catalogs; and Scenario C: Performing Infrequent Tasks. These scenarios assume that the writers and books tables have been added to the pubs database.

The writers table illustrates the columns that are candidates for full-text indexing, specifically the varchar data type.

Column name	Data type	Enable full-text features?
writer_name	char(40)	No
citizenship	char(40)	No
organization	varchar(100)	Yes
royalties_ytd	decimal(7,2)	No
royalyties_lifetime	decimal(7,2)	No
bio	varchar(500)	Yes
writer_id	integer (with a unique value enforced by the writer_id_index index)	No
last_changed	timestamp	No

The books table illustrates the columns that are candidates for full-text indexing, specifically the varchar and text data types.

Column name	Data type	Enable full-text features?
writer_name	char(40)	No
title	varchar(120)	Yes
size	smallinit	No
pub_date	datetime	No
the_words	text	Yes
abstract	varchar(500)	Yes
isbn	char(15) (with a unique value enforced by the isbn_index index)	No
last_changed	timestamp	No

For more information about both querying and indexing, see Example of Combining Full-text Administration and Full-text Query.

The second method of administering full-text indexes is through the Full-text Indexing Wizard and context menus in SQL Server Enterprise Manager. Tasks for both methods follow.

To enable a database for full-text indexing

To enable a table for full-text indexing

To enable a column for full-text indexing

To edit a full-text index on a table

To remove a full-text index from a table

To create a full-text catalog

To rebuild a full-text catalog

To rebuild all full-text catalogs in a database

To start and stop a full or incremental population of a full-text index

To check the status, tables, and schedules of a full-text catalog

To change or create a new schedule of a full-text catalog

To remove a full-text catalog from a database

To remove all full-text catalogs from a database

To clean up a full-text catalog on a server

To repopulate all full-text catalogs for a database