The following excerpt, courtesy of Que Certification, is from
Chapter 10 of the book MCAD/MCSD/MCSE Training Guide (70-229): SQL Server 2000 Database Design and Implementation by Thomas Moore and Ed Tittel. Click for the complete book excerpt series or purchase the book.
STEP BY STEP - 10.10
Using the Full-Text Wizard
- Initiate the Full-Text Wizard by selecting the Pubs database on the left pane and then selecting Full-Text Indexing from the Tools menu.
- The first screen describes the aims of the wizard. Having read them, click Next to enter the Database Selection screen. In this, you select the table you want to index. The table that you select can be among the tables found in the Pubs database. In this case, select the dbo.pub_info table and click Next.
- This screen advises you that there must already be a unique index associated with the table if you are going to continue. The wizard locates one for you, UPKC_pubinfo. This index is the unique Primary Key for the pub_info table.
- After clicking Next, you can choose the columns on which you want full-text indexing to be enabled. In this case, select the check box next to pr_info (because it is a column that contains text data) and click Next.
- Before using full-text searches, you must make a full-text catalog or choose from an existing one listed. You don't have any existing catalogs, so type Catalog Pubs in the New Catalog Name box as shown in Figure 10.10, and click Next to proceed.
- This screen enables you to choose the population schedule type. You can schedule repopulation for a whole catalog at once or a table at a time. Choose New Catalog Schedule to repopulate this catalog.
Full-Text Wizard: Creating a new catalog.
- The next screen enables you to specify options to populate the new catalog. You can choose the time when SQL Server repopulates the new catalog you have created: either when the Server agent starts or at a time specified. Type Populate Pubs as the name and leave all other settings with their defaults as shown in Figure 10.11 [Scheduling population of Catalog Pubs]. Click OK and then Next.
- The final screen shows you a summary of the decisions you made. Click Finish to end the wizard.
After completing Step by Step 10.10, you have to populate your fulltext catalog. To do this, open SQL Server Enterprise Manager from the Start menu.
Expand Server Group, expand your server, expand databases, and then expand Pubs. Click Full-Text Catalogs, and then right-click Catalog Pubs. Select Start Full Population
Scheduling population of Catalog Pubs
You can now search for words using the FREETEXT, CONTAINS, CONSTAINSTABLE, and FREETEXTTABLE statements, which unleash search power beyond that of the LIKE predicate. To search for the word Book in the pr_info column using FREETEXT, you would enter code as shown in Figure 10.12 [Making sure full-text indexing succeeded].
Making sure full-text indexing succeeded
Click for the complete book excerpt series.
This was first published in January 2006