Creates a new text index on a given table.

Syntax

---

CREATE TEXT INDEX <Name> ON <TableName>
(<ColumnName> [COLLATE <CollationName>])
[DESCRIPTION <Description>]
[INDEXED WORD LENGTH <WordLength>]
[FILTER TYPE COLUMN <ColumnName>]
[WORD GENERATOR <WordGeneratorName>]
[NO BACKUP FILES]

Usage

---

Use this statement to create a new text index on a table column. Please see the Text Indexing topic for more information.

The NO BACKUP FILES clause is optional. Unless this clause is specified, ElevateDB will create backup files (*.old) of any physical table files that were altered during the execution of the statement. Also, this clause does not apply to physical backup files created for the database catalog, which are always created and retained.

Examples

---

-- The following statement creates a text index on the
-- Notes column in the Customer table.  Notice that the collation
-- for the Notes column is overridden with the case-insensitive
-- ANSI collation.

CREATE TEXT INDEX "Notes" ON "Customer"
(Notes COLLATE ANSI_CI)
INDEXED WORD LENGTH 20

-- The following statement creates a text index on the
-- Notes column in the Customer table.  In this example,
-- however, another column called TextType is used to
-- determine the type of text in the Notes column so that
-- it can be properly filtered using a text filter.  This
-- will allow us to store HTML, RTF, and other non-plain
-- text in the Notes column and have it be indexed properly.

CREATE TEXT INDEX "Notes" ON "Customer"
(Notes COLLATE ANSI_CI)
INDEXED WORD LENGTH 20
FILTER TYPE COLUMN "TextType"

Required Privileges

---

The current user must be granted the CREATE privilege on the specified table in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: