Full-text Indexing Support

This illustration shows the components that make up the full-text indexing support. These are the components involved in defining, creating, and populating full-text indexes.

Enabling databases and tables for full-text indexing, defining and populating the indexes is specified using:

• SQL Server Enterprise Manager.

  One of the nodes of a database tree in the SQL Server Enterprise Manager GUI is used to manage the full-text catalogs in the database.

• Applications using SQL Distributed Management Objects (SQL-DMO).

  SQL-DMO has objects for managing full-text catalogs and indexes.

• Applications using Transact-SQL and a standard database API.

  Transact-SQL has a set of system stored procedures for managing full-text catalogs and indexes.

The other components are involved in defining and populating full-text indexes in this manner:

1. A Microsoft® SQL Server™ database is enabled for full-text indexing.
2. The full-text catalogs for the database are specified.
3. Individual tables are enabled for full-text indexing and associated with a catalog.
4. Individual columns in each table are added to the full-text index for the table. All the metadata information from steps 1 through 4 is stored in system tables in SQL Server databases.
5. The full-text indexes for each table are activated on a table-by-table basis. When a full-text table index is activated, a start seed value is sent from SQL Server to the indexing service within the Microsoft Search service. The start seed value identifies the table involved in the full text index.
6. Population is requested on a catalog-by-catalog basis. Each catalog population request is sent to the indexing service.
   • The indexing service passes the appropriate start seed value to the SQL Server Handler.
   • The SQL Server Handler is a driver containing logic to extract text data from the SQL Server columns involved in a full-text index. The Handler retrieves the data from SQL Server and passes it back to the index service.
   • The indexing service then passes an index identifier and indexable strings to the index engine. The index engine eliminates noise-words such as a, and, or the. It also determines the word boundaries and builds a full-text index covering the words passed down from the indexing service. The full-text index is stored in the full-text catalog file.
   • At the end of the population the indexing service calculates a new start seed value that records the point at which a subsequent incremental population should start.

The population in Step 6 can take different forms.

• Full population

  Index entries are built for all the rows in all the tables covered by the full-text catalog. A full population typically occurs when a catalog is first populated.

• Incremental population

  Only adjusts index entries for rows that have been added, deleted, or modified after the last population. This ability requires that the indexed table have a column of the timestamp data type. If the table does not have a timestamp column, only full populations can be performed. Requests for incremental populations on tables without timestamp columns result in a full population operation.

  If a new full-text index is defined for a table that has not been associated with the catalog before, the next incremental population request for the catalog builds all the entries for the table.

  Incremental population requests are implemented as full populations if any of the metadata for the table has changed since the last population. This includes altering any column, index, or full-text index definitions.

Typically, a full-text catalog will be defined and initially have a full population executed. Incremental population operations are then run at intervals to pull across new data that has been added to the indexed columns in SQL Server. These periodic incremental population operations are usually defined as jobs and are dispatched automatically by the SQLServerAgent service.

See Also