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:
-
Create a full-text catalog to store full-text indexes.
-
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. |
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.
