• Article

Full-Text Indexing Overview

The information in full-text indexes is used by the Full-Text Engine to compile full-text queries that can quickly search a table for particular words or combinations of words. A full-text index stores information about significant words and their location within one or more columns of a database table. A full-text index is a special type of token-based functional index that is built and maintained by the Full-Text Engine for SQL Server. The process of building a full-text index differs from building other types of indexes. Instead of constructing a B-tree structure based on a value stored in a particular row, the Full-Text Engine builds an inverted, stacked, compressed index structure based on individual tokens from the text being indexed. In SQL Server 2008, the size of a full-text index is limited only by the available memory resources of the computer on which the instance of SQL Server is running.

Beginning in SQL Server 2008, the full-text indexes are integrated with the Database Engine, instead of residing in the file system as in previous versions of SQL Server. For a new database, the full-text catalog is now a virtual object that does not belong to any filegroup; it is merely a logical concept that refers to a group of the full-text indexes. Note, however, that during upgrade of a SQL Server 2000 or SQL Server 2005 database, any full-text catalog that contains data files, a new filegroup is created; for more information, see Full-Text Search Upgrade.

Note

In SQL Server 2008, the Full-Text Engine resides in the SQL Server process, rather than in a separate service. Integrating the Full-Text Engine into the Database Engine improves full-text manageability, optimization of mixed query, and overall performance.

Only one full-text index is allowed per table. For a full-text index to be created on a table, the table must have a single, unique nonnull column. You can build a full-text index on columns of type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max) can be indexed for full-text search. Creating a full-text index on a image, varbinary, or varbinary(max) requires that you specify a type column. A type column is a table column in which you store the file extension (.doc, .pdf, .xls, and so forth) of the document in each row.

A good understanding of the structure of a full-text index will help you understand how the Full-Text Engine works. For more information, see Full-Text Index Structure.

The process of creating and maintaining a full-text index is called a population (also known as a crawl). There are three types of full-text index population: full population, change tracking-based population, and incremental timestamp-based population. For more information, see Full-Text Index Population.

To create a full-text index

To alter a full-text index

To drop a full-text index