Use sp_create_text_index to create text indexes. sp_create_text_index:
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 Enhance Full-Text Search engine must be running
to successfully execute sp_create_text_index.
For information on starting and stopping the Enhanced Full-Text
Search engine, see Chapter 6, “System Administration.”
The text index can contain up to 16 columns. Columns of the following datatypes can be indexed: char, varchar, nchar, nvarchar, date, time, 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 Enhanced 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 (see 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 also expedites the process. See “batch_size” for more information.
Do not rename an index; the Verity collection will not
be renamed.
