Setting Up a Full-Text Index

You can set up a full-text index using system-stored procedures (including sp_fulltext_database, sp_fulltext_table, and so on), or through the SQL Enterprise Manager. The examples here demonstrate how to set up an index using the Enterprise Manager.

Suppose you are working on a project for a technical recruiting firm that wants to have a searchable database of candidate résumés. You have a table called resume, with the following definition:

create table resume( 
       resume_id int identity
              constraint PK_resume primary key clustered,
        contact_id int not null
               constraint FK_resume_contact references contact,
        submitDT datetime not null default CURRENT_TIMESTAMP,
       res_text text null

In the preceding definition, contact_id refers to a separate table that contains details about the person whose résumé is listed in res_text. You would look in this table to find a person's phone number and email address. The res_text column is the column that will need to be full-text indexed.

To create a full-text index on this table, start by drilling down through the SQL Server's name, Databases, ResumeDB, Tables, then selecting the résumé table from the list. From the Tools menu in Enterprise Manager, select Full-Text Indexing. This will load the Full-Text Wizard. The wizard will lead you through the steps for enabling a full-text index on the table. You must complete each of these steps:

  1. Identify the database and table you want to index. In this example, you have selected the résumé table.

  2. Identify the unique index you want to use. It is not necessary to have a primary key constraint on the table, but full-text indexing requires a unique index on non-nullable columns to support its own indexes. Each index entry will include a pointer to the primary key record where the word exists.

    In this example, select the primary key PK_resume.

  3. The next step is to identify the columns containing data to be indexed. Normally, a table contains just a single text column, but it is possible to index more than one column per table.

    In this example, select the text column res_text.

  4. Select a name and file system location for the new full-text catalog. A catalog can contain information for many tables, and a database can contain many catalogs. The catalog name is used when querying to identify which full-text index should be searched.

    In this example, check the dialog box for Create New Full-Text Catalog. You will have the option to indicate where in the file system to place the full-text catalog.

  5. Optionally, you can set up a repopulation schedule for the index. More information on population is provided later in the chapter, in the section titled "Choosing the Best Refresh Schedule."


The Microsoft Search Server is a separate service. If you are attempting to create full-text indexes from within SQL Enterprise Manager, and the options are grayed out, ensure that the Search Server service is running.


Full-text search needs a single unique index on a table to be able to work. A composite primary key consisting of two or more columns will not work. If you have tables that do not have a single column unique index, you will need to add a new, unique column to the table. Identity columns work well for this.

If the table has more than one unique index, use the smallest, most narrow available index to get the best performance. For example, if you had a table with a unique GUID column and a unique integer 4 bytes each, instead of the GUID at 16 bytes each.

At this point, you have set up the index, but it is not yet populated. In Figure 44.1, I have set up a full-text index on resume in a catalog named FTCatalog. From the Action menu, select Start Full Population. This starts the actual indexing process.

Figure 44.1. Enterprise Manager showing full-text Indexing Population menu.


Now that you have a full-text index created and populated, it is ready for use.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features