Use the sp_create_text_index system procedure to create the text indexes. sp_create_text_index does the following:
Updates the vesaux and vesauxcol tables in the text_db database
Creates the text index (Verity collections)
Populates the Verity collections
Creates the index table in the user database where the source table is located
The text index can contain up to 16 columns. Columns of the following datatypes can be indexed:
char, varchar, nchar, nvarchar, text, image, datetime, smalldatetime, int, smallint, tinyint, unichar, and univarchar.
For example, to create a text index and an index table named i_blurbs for the copy column in the blurbs table in pubs2 on KRAZYKAT, enter:
sp_create_text_index "KRAZYKAT", "i_blurbs", "blurbs", " ", "copy"
where:
KRAZYKAT is the name of the Full-Text Search engine
i_blurbs is the name of the index table and text index you are creating
blurbs is the source table on which you are creating the text indexes
" " is a placeholder for text index creation options
copy is the column in the blurbs table that you are indexing
See sp_create_text_index for more information.
Make sure the text_db database
name in the configuration file (listed after the defaultDb parameter)
matches the database name in Adaptive Server. If they do not match, the
text index cannot be created. Also, verify that the text_events table
exists in the user database. If it does not exist, run the installevent script
for that database (refer to “Running the installevent Script”).
Populating the Verity collections can take a few minutes or several hours, depending on the amount of data you are indexing. You may want to perform this step when the server is not being heavily used. Increasing the batch_size configuration parameter will also expedite the process. See “batch_size” for more information.
Do not rename an index because the Verity collection
will not be renamed.
