Getting Started with Full-Text Search

  • Article

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.

Note

Databases in SQL Server are full-text enabled by default, unless they are created by using SQL Server Management Studio. To enable a database for full-text search when you create a database by using Management Studio, see How to: Create a Database (SQL Server Management Studio). To enable an existing database for full-text search, see How to: Enable a Database for Full-Text Indexing (SQL Server Management Studio).

Note

You can also create full-text indexes on indexed views. For more information about indexed views, see Types of Views.

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.

Note

To specify the location of the directory, use the IN PATH clause. For more information, see CREATE FULLTEXT CATALOG (Transact-SQL).

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)

Note

The Document table already has such a column. The previous code is for demonstration purposes only.

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

Release History

17 July 2006
New content:
  • Added the Note that states when databases are created by using Management Studio, they are not enabled, by default, for full-text indexing.