Export (0) Print
Expand All

Getting Started with Full-Text Search

Updated: 17 July 2006

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.
ms142497.note(en-US,SQL.90).gifNote:
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).

ms142497.note(en-US,SQL.90).gifNote:
You can also create full-text indexes on indexed views. For more information about indexed views, see Types of Views.

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.

ms142497.note(en-US,SQL.90).gifNote:
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)
ms142497.note(en-US,SQL.90).gifNote:
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.

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.

Community Additions

ADD
Show:
© 2014 Microsoft