Creates a text index.
sp_create_text_index server_name, index_table_name, table_name, “batch”, column_name [, column_name ... ]
– is the name of the Full-Text Search engine.
– is the name of the index table. index_table_name has the form [dbname.[owner.]]table, where:
dbname is the name of the database containing the index table.
owner is the name of the owner of the index table.
table is the name of the index table.
– is the name of the source table containing the text being indexed. table_name has the form [dbname.[owner.]]table.
– The “batch” operator (must be in quotes) tells the Full-Text Search to reallocate every session after each batch sent to the VDK.
– is the name of the column indexed by the text index.
sp_create_text_index "blue", "i_blurbs", "blurbs", " ", "copy"
Creates a text index and an index table named i_blurbs on the copy column of the blurbs table.
Up to 16 columns can be indexed in a single text index.
Columns of the following datatypes can be indexed: char, varchar, nchar, nvarchar, text, image, datetime, and smalldatetimeint, smallint, and tinyint
The content of option_string is not case sensitive.
option_string uses a null string (" ") to specify “No Options”.
Assign the value “empty” to option_string to create a text index that you will immediately drop. This creates the Verity collection directory and the style files, but does not populate the collections. For example, when you configure an individual table for clustering, you create the text index and immediately drop it. After you edit the style.prm file, you re-create the text index. For more information, see “Editing Individual style.prm Files”.
sp_create_text_index writes entries to the vesaux table and tells the Full-Text Search engine to create the text index.
Execution of sp_create_text_index is synchronous. The Adaptive Server process executing this system procedure remains blocked until the index is created. The time required to index large amounts of data may be take as long as several hours to complete.
When you create a text index on two or more columns, each column in the text index is placed into its own document zone. The name of the zone is the name of the column. The zones can be used to limit your search to a particular column. For more information, see “in”.
Do not rename an index after creating.
Can’t run sp_create_text_index from within a transaction
’column_name’ cannot be NULL.
Column ’column_name’ does not exist in table ’table_name’
Index table mapping failed - Text Index creation aborted
Invalid text index name - ’index_name’ already exists
’parameter’ is not in the current database
Server name ’server_name’ does not exist in sysservers.
’table_name’ does not exist
’table_name’ is not a valid object name
Table ’table_name’ does not have an identity column - text index creation aborted
Text index creation failed
User ’user_name’ is not a valid user in the database
Any user can execute sp_create_text_index.
