Expand News
IS Administration
Expand MMC
Expand Com Objects
Expand IDA/IDQ
IS Programming
Expand IS Programming
IS TroubleShooting
Expand Indexing
Expand Querying
IS Optimizing
Expand Querying
Expand Indexing
IS Links
Expand MSDN
Expand TechNet
Expand Other MS
Expand Other Links
Expand iFilters
SQL 2000 FTS
Expand Loading Data
Expand Indexing
Using the Wizard
Expand Querying
Expand SQL FTS Links
SQL 2005 FTS
Expand What's New

Index Server Faq

Using the Microsoft SQL FTS Wizard

Connect to your SQL Server using Enterprise Manager. Expand your database, and right click on the table you wish to full text index (FTI). Point to Full Text Index table, and click Define Full-Text Indexing on a Table (as illustrated in Figure 1).

Figure 1.

This will launch the Welcome to the SQL Server Full-Text Indexing Wizard (Figure 3).

If the Full-Text Index Table menu option is not enabled as illustrated in Figure 2 click here

Figure 2.

If the menu option is enabled you will get the Welcome to the SQL Server Full-Text Indexing Wizard (Figure 3)

Figure 3.

Click Next. You may get a dialog which tells you that you do not have a unique index on the table you are trying to full text index (Figure 4). If so click here for instructions on how to add the unique index.

Otherwise you will get the Select and Index dialog box displayed in Figure 5.

Figure 4.

Click Finish and add a unique index to your table. Click here for instructions on how to do this.

Otherwise you will get the Select and Index dialog box displayed in Figure 5.

In the drop down list select a unique index. Your choice of index will depend on the update activity on the column which comprises this index. Ideally you want a column which is not updated.

Figure 5.

When you have selected your unqiue index, click Next to launch the Select Tables dialog box, illustrated in Figure 6.

Figure 6.

Select the columns that you wish to Full Text Index. You do this by clicking the check box to the left of the table name. After you have selected a table to be indexed you will get the Language for Word Breaker drop down list box as illustrated in Figure 7.

Figure 7.

In the drop down list select the language which best represents the language your users will be querying in. Note that due to a "bug" in the GUI the Spanish word breaker does not appear here. To index columns using Spanish click here for TSQL commands to carry this out.

The word breakers and noise word lists for the US English and UK English word breakers are the same. There is a diffferent stemmer class however. This means that when you issue FreeText or Inflectional queries you may see differences for certain queries when your index using US English vs UK English.

When you have finished selecting the columns you wish to FTI and selected the appropriate language word breaker click on Next. This will launch the Select a Catalog dialog box (Figure 8).

If you are indexing content which you have stored in its native format in the Image data type column you can have the iFilter for that document type applied to the contents of that row. This means you can have Word Documents stored in the image data type columns indexed natively. Otherwise your only option would be to convert these Word documents to text. Only the contents will be indexed, no properties.

Please click on here for a list of iFilters.

Click on Next to launch the Select a Catalog dialog box. This allows you to select the catalog you wish provide indexing and querying services for your catalog. In general large tables should go in their own catalog.

Figure 8.

Use the Select full-text catalog drop down box to select the catalog you wish to park your table in, or check the Create a new catalog check box, and enter a name of your catalog. This name should be a unique catalog name for the database. The location by default will be C:\Program Files\Microsoft SQL Server\MSSQL($InstanceName)\FtData. Ideally you will place your catalog on a RAID 10 array which has a seperate controller. Ensure that your page file is not on this array. Click Next to launch the Select or Create Population Schedules (Optional) dialog box (figure 9.).

Figure 9.

The Select or Create Population Schedules (Optional) dialog box allows you to schedule your popultions. Clicking on the New Table Schedule button will launch the New Full-Text Indexing Table Schedule dialog box (Figure 10).


Figure 10.

In the New Full-Text Indexing Table dialog box you can set the schedule you wish the catalog to be built at, as well as the type of population. You can also assign a name to your schedule for easy identification in the Management folder of Enterprise Manager for your SQL Server.

Ideally you will want to use change tracking with update index in background (this option is not accessible in this dialog). You can access it by right clicking on your table, and selecting Full-Text Index Table and Change Tracking (figure 15-17). However, you can also use a Full or Incremental populations. Full and Incremental populations can be lengthy. If a large portion of your data changes you may find that an Incremental population can take longer than a Full population. Both Incremental and Full populations generally take almost the same amount of time. If at all possible use Change Tracking with Update Index in Background. You can also schedule an Index Update - this option is used if you are using Change Tracking. After you have completely built your schedules, click on OK. You will return to the scheduling Select or Create Population Schedules (Optional) dialog box illustrated in Figure 9. Your schedules will now appear (Figure 11).

Figure 11.

Click on Next to launch the Completion Splash Screen (Figure 12)

Figure 12.

A summary of your Full-Text indexing configuration will appear. You have the option to return to previous dialogs using the Back button. Click on Finish to complete the dialog. You will see status messages (Figure 13,  and 14).

Figure 13.

Figure 14.

You have now configure Full-Text indexing on your table. You must now run a full population to index your table. To do this, return your database in EM, and right click on your table, select Full-Text Index Table, and Start Full Population as illustrated in Figure 15.

Figure 15.

You can also select Change Tracking as illustrated in Figure 16. After selecting Change Tracking you must either schedule an update of the index, by right clicking on your table again, selecting Full-Text index Table, and then clicking Schedules . This will load the same (or similar) dialogs as you saw in Figures 9 & 10.

Figure 16.If you select Change Tracking with the Update Index in Background option, you must then right click again on your table, select Full-Text index table, and click Update Index in Background as illustrated in Figure 17.

Figure 17.

SQL Server 2000 FTS allows you to index and search documents stored in their native format if

1) you store the documents in columns using the Image datatype

2) you add a column to your table called the Document type column. This column should be char(3) or char(4) and its value should correspond to the file extension your document would have if it was stored in the file system. For instance if your document is a Word document, if this document was stored in the file system it would have an extension of .doc. Therefore your document type column value for this row would be doc or .doc.

3) You will have use the Full Text Indexing Wizard that the documents stored in the image column have their document type values stored in the column you designated as your document type column. You may have to click on the word breaker drop down box to enable the Document type column drop down box. This is illustrated in Figure 18.

Figure 18.

This completes the configuration you will have to do to enable SQL FTI (Full-Text Indexing) or your tables. You should now be ready to start querying them.


designed by :: smilla group