Full-Text Indexes
Full-text support for Microsoft® SQL Server™ 2000 data involves two features: the ability to issue queries against character data, and the creation and maintenance of the underlying indexes facilitating these queries.
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 are allowed. | Only one full-text index per table is allowed. |
| Updated automatically when the data upon which they are based is inserted, updated, or deleted. | Addition of data to full-text indexes, called population, can be requested through either a schedule or a specific request, or can occur automatically with the addition of new data. |
| Not grouped. | Grouped within the same database into one or more full-text catalogs. |
| Created and dropped using SQL Server Enterprise Manager, wizards, or Transact-SQL statements. | Created, managed, and dropped using SQL Server Enterprise Manager, wizards, or 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.
Note The full-text engine runs as a service named Microsoft Search on Microsoft Windows NT® Server and Microsoft Windows® 2000 Server. The Microsoft Search service is not available for Microsoft SQL Server Personal Edition. Although this means the Microsoft Search service is not installed on Microsoft Windows 95 or Windows 98, Windows NT Workstation, or Windows 2000 Professional clients, these clients can make use of the service when they are connected to an instance of SQL Server Standard or Enterprise edition.
- Database
A database must be enabled to use the full-text service. Meta data 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. Each catalog can serve the indexing needs of one or more tables within 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 instance of SQL Server. Removable drives, floppy disks, and network drives are not supported.) A maximum of 256 full-text catalogs can be created on each server.
Note Full-text indexing is fully supported in a Windows NT failover cluster environment. For more information, see Running Full-Text Queries with Failover Clustering.
- Table
A table must first be enabled for full-text support. Then meta data, such as the name of the table and its full-text catalog, is created for the full-text index associated with the table. After 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 meta data and status information.
Like regular SQL indexes, full-text indexes can be automatically updated as data is modified in the associated tables. Alternatively, full-text indexes can be repopulated manually at appropriate intervals. This repopulation can be time-consuming and resource-intensive; therefore, it is an asynchronous process that usually runs 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. By setting up full-text catalog population schedules in this way, full-text indexes 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 4-byte, integer-based index is optimal.) This reduces the resources required by Microsoft Search service in the file system significantly. 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 reaches the maximum size allowed (450 bytes), full-text population will 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.