Administering Full-Text Search
Full-text administration can be separated into four main tasks:
- Creating full-text indexes and full-text catalogs.
- Altering existing full-text indexes and catalogs.
- Dropping existing full-text indexes and catalogs.
- Scheduling and maintaining index population.
Full-text administration is carried out at the server, database, table and column levels. At the server level you can set certain server-wide properties, such as resource_usage, to increase or reduce the amount of system resources used by the full-text service. At the database level you can enable or disable a database to use the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service. You can create and drop the metadata for one of more full-text catalogs in a full-text enabled database.
Note
In Microsoft SQL Server 2005, all databases are full-text enabled by default.
At the table level you can enable or disable a table 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 full-text search, you will need to repopulate the associated full-text catalog to synchronize the full-text index with the new full-text definition. At the column level you can add or drop columns that support full-text queries.
Considerations for Creating Full-Text Indexes and Full-Text Catalogs
Before learning about creating full-text indexes, it is important to know how they differ from regular SQL Server indexes. The following table lists these differences.
| Full-text indexes | Regular SQL Server indexes |
|---|---|
Stored in the file system, but administered through the database. |
Stored under the control of the database in which they are defined. |
Only one full-text index allowed per table. |
Several regular indexes allowed per table. |
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. |
Updated automatically when the data upon which they are based is inserted, updated, or deleted. |
Grouped within the same database into one or more full-text catalogs. |
Not grouped. |
At all these levels, facilities are available to retrieve metadata and status information.
Like regular SQL Server indexes, full-text indexes can be automatically updated as data is modified in the associated tables. Alternatively, you can repopulate full-text indexes manually at appropriate intervals, but this can be time-consuming and resource-intensive. Therefore, index updating is usually performed as an asynchronous process that 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 exceeds the maximum size allowed (900 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.
See Also
Concepts
Full-Text Indexes
Introduction to Full-Text Search
Full-Text Search Architecture
Querying SQL Server Using Full-Text Search