REFRESH TEXT INDEX Statement

Syntax

REFRESH TEXT INDEX text-index-name ON [ owner.]table-name
   [ WITH {
      ISOLATION LEVEL isolation-level 
      | EXCLUSIVE MODE 
      | SHARE MODE } ]
   [ FORCE { BUILD | INCREMENTAL } ]

Parameters

• WITH clause – use the WITH clause to specify what kind of locks to obtain on the underlying base tables during the refresh. The types of locks obtained determine how the text index is populated and how concurrency for transactions is affected. If you do not specify the WITH clause, the default is WITH ISOLATION LEVEL READ UNCOMMITTED, regardless of any isolation level set for the connection.
  You can specify the following WITH clause options:

  • ISOLATION LEVEL isolation-level – Use WITH ISOLATION LEVEL to change the isolation level for the execution of the refresh operation.

    The original isolation level of the connection is restored at the end of the statement execution.

  • EXCLUSIVE MODE – Use WITH EXCLUSIVE MODE if you do not want to change the isolation level, but want to guarantee that the data is updated to be consistent with committed data in the underlying table. When using WITH EXCLUSIVE MODE, exclusive table locks are placed on the underlying base table and no other transaction can execute queries, updates, or any other action against the underlying table(s) until the refresh operation is complete. If table locks cannot be obtained, the refresh operation fails and an error is returned.
  • SHARE MODE Use WITH SHARE MODE to give read access on the underlying table to other transactions while the refresh operation takes place. When this clause is specified, shared table locks are obtained on the underlying base table before the refresh operation is performed and are held until the refresh operation completes.
• FORCE clause – use this clause to specify the refresh method. If this clause is not specified, the database server decides whether to do an incremental update or a full rebuild based on how much of the table has changed.
  • FORCE BUILD clause Refreshes the text index by recreating it. Use this clause to force a complete rebuild of the text index.
  • FORCE INCREMENTAL clause Refreshes the text index based only on what has changed in the underlying table. An incremental refresh takes less time to complete if there have not been a significant amount of updates to the underlying table. Use this clause to force an incremental update of the text index.

    An incremental refresh does not remove deleted entries from the text index. As a result, the size of the text index may be larger than expected to contain the current and historic data. Typically, this issue occurs with text indexes that are always manually refreshed with the FORCE INCREMENTAL clause. On automatically refreshed text indexes, historic data is automatically deleted when it makes up 50% of the total size of the text index.

Examples

• Example – The following statement refreshes a fictitious text index called MarketingTextIndex, forcing it to be rebuilt.

  REFRESH TEXT INDEX MarketingTextIndex ON GROUPO.MarketingInformation 
      FORCE BUILD;

Usage

When using the FORCE clause, you can examine the results of the sa_text_index_stats system procedure to decide whether a complete rebuild (FORCE BUILD), or incremental update (FORCE INCREMENTAL) is most appropriate.

You cannot execute the REFRESH TEXT INDEX statement on a text index that is defined as IMMEDIATE REFRESH.

For MANUAL REFRESH text indexes, use the sa_text_index_stats system procedure to determine whether the text index should be refreshed. Divide pending_length by doc_length, and use the percentage as a guide for deciding whether a refresh is required. To determine the type of rebuild required, use the same process for deleted_length and doc_count.

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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

• ALTER ANY INDEX system privilege.
• ALTER ANY OBJECT system privilege.
• REFERENCES privilege on the table.
• You own the table.