Getting Started with Full-Text Search

Setting up full-text indexing capability on a table in Microsoft SQL Server 2005 is a two-step process:

1. Create a full-text catalog to store full-text indexes.
2. Create full-text indexes.

Setting Up Full-Text Search in AdventureWorks

The following example creates a full-text index on the Document table in AdventureWorks.

To create a full-text catalog named AdvWksDocFTCat, use the CREATE FULLTEXT CATALOG statement:

CREATE FULLTEXT CATALOG AdvWksDocFTCat

This statement creates the full-text catalog in the default directory specified during setup. The folder named AdvWksDocFTCat is in the default directory.

Alternatively, you can use Management Studio to create a full-text catalog. In Object Explorer, under the database in which you want to create the full-text catalog, expand the Storage folder, right-click the Full-Text Catalogs folder, and then click New Full-Text Catalog. For more information, see New Full-Text Catalog (General Page).

Full-text catalog IDs start at 00005 and are incremented by one for each new catalog created.

As a best practice, if possible, full-text catalogs should be created on their own physical drive (or drives). Given the process of building a full-text index is fairly I/O intensive (on a high level, it consists of reading data from SQL Server, and then writing the index to the file system), you probably want to avoid letting your I/O subsystem become a bottleneck.

Now that you have created the full-text catalog, the next step is to create a full-text index. But, before you can create a full-text index on the Document table, you have to determine whether the table has a unique, single-column, non-nullable index. The Microsoft Full-text Engine for SQL Server (MSFTESQL) service uses this unique index to map each row in the table to a unique, compressible key.

To create a unique, single-column, non-nullable index, run the following statement:

CREATE UNIQUE INDEX ui_ukDoc ON Production.Document(DocumentID)

Now that you have a unique key, you can create a full-text index on the Document table.

CREATE FULLTEXT INDEX ON Production.Document
(
    Document                         --Full-text index column name 
        TYPE COLUMN FileExtension    --Name of column that contains file type information
        Language 0X0                 --0X0 is LCID for neutral language
)
KEY INDEX ui_ukDoc ON AdvWksDocFTCat --Unique index
WITH CHANGE_TRACKING AUTO            --Population type
GO

Alternatively, you can create a full-text index by using the Full-Text Indexing Wizard. For more information, see How to: Launch the Full-Text Indexing Wizard (SQL Server Management Studio).

For information about things to consider when you are choosing the column language, see International Considerations for Full-Text Search.

To monitor the population status use either the FULLTEXTCATALOGPROPERTY or OBJECTPROPERTYEX functions. To get find the catalog population status, run the following statement:

SELECT FULLTEXTCATALOGPROPERTY('AdvWksDocFTCat', 'Populatestatus')

Typically, if a full population is in progress, the result returned is 1.

See Also

Other Resources

FULLTEXTCATALOGPROPERTY (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
CREATE FULLTEXT CATALOG (Transact-SQL)
CREATE FULLTEXT INDEX (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History