CREATE TEXT INDEX statement

CREATE TEXT INDEX [ IF NOT EXISTS ] text-index-name 
ON [ owner.]table-name( column-name, ... ) 
 [ IN dbspace-name ]
 [ CONFIGURATION [ owner.]text-configuration-name ]
 [ { IMMEDIATE REFRESH 
      | MANUAL REFRESH 
      | AUTO REFRESH [ EVERY integer { MINUTES | HOURS } ] ]
    }

IF NOT EXISTS clause When the IF NOT EXISTS clause is specified and the named text index already exists, no changes are made and an error is not returned.
ON clause Use this clause to specify the table and columns on which to build the text index.
IN clause Use this clause to specify the dbspace in which the text index is located. If this clause is not specified, then the text index is created in the same dbspace as the table it references.
CONFIGURATION clause Use this clause to specify the text configuration object to use when creating the text index. If this clause is not specified, the default_nchar text configuration object is used if any of the columns in the index are NCHAR; otherwise, the default_char text configuration object is used.
REFRESH clause Use this clause to specify the refresh type for the text index. If you do not specify a REFRESH clause, IMMEDIATE REFRESH is used as the default. Following are the list of refresh types you can specify:
- IMMEDIATE REFRESH Specify IMMEDIATE REFRESH to refresh the text index each time changes in the underlying table impact data in the text index.
- AUTO REFRESH Use this clause to refresh the materialized view automatically using an internal server event. Use the EVERY subclause to specify the refresh interval in minutes or hours. If you specify AUTO REFRESH without supplying interval information, the database server refreshes the text index every 60 minutes. A text index may be refreshed earlier than specified by the AUTO REFRESH clause if the pending_size value, as returned by the sa_text_index_stats system procedure, exceeds 20% of the text index size at the last refresh or if the deleted_length exceeds 50% of the text index size. An internal event executes once per minute to check this condition for all AUTO REFRESH text indexes.
- MANUAL REFRESH Use this clause to specify that the text index is refreshed manually.
For more information about refresh types, see Text index refresh types.

You cannot create a text index on views, materialized views, or temporary tables.

An IMMEDIATE REFRESH text index is populated at creation time and an exclusive lock is held on the table during this initial refresh. IMMEDIATE REFRESH text indexes provide full support for queries that use snapshot isolation.

MANUAL and AUTO REFRESH text indexes must be initialized (refreshed) after creation.

Refreshes for AUTO REFRESH text indexes scan the table using isolation level 0. See isolation_level option.

Once a text index is created, you cannot change it to, or from, being defined as IMMEDIATE REFRESH. If either of these changes is required, you must drop and recreate the text index.

You can choose to manually refresh an AUTO REFRESH text index using the REFRESH TEXT INDEX statement. See REFRESH TEXT INDEX statement.

To view text indexes and the text configuration objects they refer to, see How to view text index info in the database.

Must be the owner of the underlying table, or have DBA authority, or have REFERENCES permission.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Automatic commit

SQL/2008 Vendor extension.

The following example creates a text index, myTxtIdx, on the Description column of the MarketingInformation table in the sample database. The MarketingTextConfig text configuration object is used, and the refresh interval is set to every 24 hours.

CREATE TEXT INDEX myTxtIdx ON MarketingInformation ( Description ) 
   CONFIGURATION default_char
   AUTO REFRESH EVERY 24 HOURS;

CREATE TEXT INDEX statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example