
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Setup full-text search: step by step guide

Alexander Chigrik
chigrik@mssqlcity.com

Introduction
General concepts
Full-text indexes vs. regular SQL indexes
Maintaining full-text indexes
SQL Server 2000 full-text search enhancements
Step by step example
Literature

Introduction

In this article, I want to tell you about some general full-text search concepts: about software requirements, about Microsoft Search Service, about maintaining full-text indexes, about the comparison of full-text indexes with regular SQL indexes, and how to setup full-text search step by step.

This document was developed against Microsoft SQL Server 7.0, but full-text search can work in SQL Server 2000 as well, and SQL Server 2000 has some full-text search enhancements.

General concepts

SQL Server 7.0 supports full-text search. This is a new feature, which was not supported in the previous versions. The full-text search allows you to create special indexes for SQL Server character-based data. These special indexes, called full-text indexes, support linguistic and proximity searches on several languages. By using full-text indexes, you can search by words, phrases, words in close proximity to each other, and by inflexional forms of verbs and nouns. The full-text indexes can be created on the following SQL Server character-based data types:

char

nchar

varchar

nvarchar

text

ntext

Note. In additional to the above data types, SQL Server 2000 can create full-text indexes of text data stored in image columns (only the text data stored in image columns can be indexed, images or pictures cannot be indexed).

The full-text search runs as a service named Microsoft Search Service (MSSearch service).

To work with full-text search, you should have the following operation systems:

Windows NT Server version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
Windows NT Server Enterprise Edition version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
Windows 2000 Server
Windows 2000 Advanced Server
Windows 2000 DataCenter

It's because the MSSearch service cannot be installed on Windows 9x, Windows NT Workstation, or Windows 2000 Professional clients, but these clients can use full-text search, when the MSSearch service installed on the server machine.

The full-text search supported under the following SQL Server editions:

SQL Server 7.0 Standard Edition

SQL Server 7.0 Enterprise Edition

SQL Server 2000 Personal Edition (except on Windows 9x)

SQL Server 2000 Standard Edition

SQL Server 2000 Developer Edition

SQL Server 2000 Enterprise Edition

SQL Server 2000 Enterprise Evaluation Edition

To install full-text search in SQL Server 7.0, you should choose Custom installation type, because full-text search is not supported under the Typical or Minimum installation types.

To install full-text search in SQL Server 2000, you should choose Typical or Custom installation types, because full-text search is not supported under the Minimum installation type.

Because working with full-text search is very resource expensive, you should have enough physical and virtual memory. Set the virtual memory size to at least 3 times the physical memory installed in the computer, and set the SQL Server max server memory server configuration option to half the virtual memory size setting (1.5 times the physical memory).

Full-text indexes vs. regular SQL indexes

The full-text indexes very differ from the regular SQL Server indexes. The full-text indexes are stored in the full-text catalogs outside of SQL Server databases and managed by the MSSearch service. Full-text catalog is a set of operation system files (the default directory determined during installation is Ftdata subdirectory in the Microsoft SQL Server directory; for example, C:\MSSQL7\Ftdata, the default directory for SQL Server 7.0; and C:\Program Files\Microsoft SQL Server\Mssql\Ftdata, the default directory for SQL Server 2000).

Unlike regular SQL indexes, only one full-text index per table is allowed. 249 nonclustered and 1 clustered regular SQL indexes are allowed per table.

Unlike regular SQL indexes, full-text indexes are not updated automatically when the data upon which they are based is inserted, updated, or deleted. To reflect these changes, you should update full-text indexes manually, or create a job to update these indexes on a scheduled basis.

Unlike regular SQL indexes, full-text indexes cannot be created, managed, or dropped using the Transact-SQL statements, only by using SQL Server Enterprise Manager, wizards, or stored procedures. For example, DROP INDEX and CREATE INDEX statements are not allowed for full-text indexes.

Maintaining full-text indexes

Because full-text indexes are not updated automatically when the data upon which they are based is inserted, updated, or deleted, you should immediately update full-text indexes when data in its associated table changes.

There are two ways under SQL Server 7.0 to synchronize full-text indexes with its table data:

Full Population

Incremental Population

Full Population - is a population, when the index entries are built for all the rows in all the full-text catalog's tables.

Incremental Population - is a population, which only adjusts index entries for rows that have been added, deleted, or modified after the last population. To make Incremental Population the indexed table must have a column of the timestamp data type. If the indexed table does not have a timestamp column, only full population can be used.

The population of full-text indexes can be made either through Enterprise Manager or through the sp_fulltext_catalog system stored procedure.

Because full-text index population can take time, these populations should be scheduled during CPU idle time and slow production periods, such as in the evenings or on weekends.

SQL Server 2000 full-text search enhancements

SQL Server 2000 introduces a new way to maintain the full-text indexes. There is Change tracking. Change tracking population maintains a log of all changes to the full-text indexed data, and propagates the changes to the full-text index. There are three Change tracking population's options:

Background

On demand

Scheduled

With the Background option, changes to rows in the table are propagated to the full-text index as they occur. You can use this option only when you have enough CPU and memory, because it can take many time.

With the On demand option, you should manually update the full-text index by using the sp_fulltext_table with the update_index for the @action parameter.

With the Scheduled option, you can use SQLServerAgent to schedule periodic jobs that execute the sp_fulltext_table system stored procedure with the update_index for the @action parameter.

This is the example to start the Change tracking with the Background option for the Product table in the Sales database:

USE Sales
GO
EXEC sp_fulltext_table 'Product', 'Start_change_tracking'
EXEC sp_fulltext_table 'Product', 'Start_background_updateindex'
GO

Note. The Change tracking does not track any WRITETEXT or UPDATETEXT operations.

Another SQL Server 2000 full-text search enhancement is image filtering. Image filtering allows you to index and query documents stored in image columns (only the text data stored in image columns can be indexed, images or pictures cannot be indexed).

Note. Though a single computer can have multiple instances of SQL Server 2000, only one MSSearch service can exist. So, a single MSSearch service manages the full-text indexes for all the instances of SQL Server 2000 on the computer.

Step by step example

To set up full-text search, you should make the following steps:

Enable the database to support full-text indexes.

Create a full-text catalog.

Register appropriate table(s) for full-text processing.

Add each column that participates in a full-text index.

Create a full-text index.

Start a full population of the full-text catalog.

Setup full-text search can be made either through Enterprise Manager or through the system stored procedures.

In this example, I will create a full text index for the pr_info column from the pub_info table in the pubs database. Because using GUI interface is much easier and understandable way to set up full-text search in comparison with using system stored procedures, I will illustrate the setup full-text search using the SQL Server Enterprise Manager.

Run SQL Server Enterprise Manager, expand a server group and expand a server. Expand Databases and click a database to enable. On the Tools menu, click Full-Text Indexing as shown in Figure 1.

Choose SQL Server Full-Text Indexing Wizard

Choose SQL Server Full-Text Indexing Wizard

Figure 1.

This will launch the SQL Server Full-Text Indexing Wizard shown in Figure 2.

Welcome to the SQL Server Full-Text Indexing Wizard

Figure 2.

Selecting the Next button will take you to the choosing of a SQL Server table for full-text indexing as shown in Figure 3.

Select a table

Figure 3.

Select the pub_info table and click the Next button. Clicking the Next button will take you to Figure 4.

Select an Index

Figure 4.

Now you must select a unique index for the pub_info table. Select UPKCL_pubinfo and click the Next button. Clicking the Next button will take you to Figure 5.

Select Table Columns

Figure 5.

Now you should select the character-based columns you want to be eligible for full-text queries. Add pr_info column and click the Next button. Clicking the Next button will take you to Figure 6.

Select a Catalog

Figure 6.

Now you must select an existing full-text catalog or create one for the pubs database. Because in our example there are no existing full-text catalogs, you must create the new fcPubs full-text catalog. Clicking the Next button will take you to Figure 7.

Select or Create Population Schedules (Optional)

Select or Create Population Schedules (Optional)

Figure 7.

At this step, you should select or create population schedules. Because in our example there are no existing population schedules, you must created the new schedule by clicking the New schedule button. Clicking this button will take you to Figure 8.

New Full-Text Indexing Schedule

Figure 8.

Specify the new population schedule's parameters. Clicking the Change button will take you to Figure 9.

Edit Recurring Full-Text Indexing Job Scheduling

Figure 9.

Edit recurring full-text indexing job scheduling as shown in Figure 9 and click the Next button. Clicking the Next button will take you to Figure 10.

Select or Create Population Schedules (Optional)

Figure 10.

Selecting the Next button will take you to the completion of the configuration as shown in Figure 11.

Completing the SQL Server Full-Text Indexing Wizard

Completing the SQL Server Full-Text Indexing Wizard

Figure 11.

SQL Server will then perform a number of operations corresponding to the options that were selected. Once completed, you should see Figure 12 confirming that Full-Text Indexing Wizard completed successfully.

Define full-text indexing

Figure 12.

Now you should start a full population of the fcPubs full-text catalog as shown in Figure 13.

Start Population

Figure 13.

Once completed, you should see Figure 14 confirming that population of full-text catalog started successfully.

Figure 14.

Literature

1. Full-Text Indexes

2. Full-Text Indexing Support

3. Full-Text Query Architecture

4. Maintaining Full-Text Indexes

5. Implementation of Full-text Search

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc