Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

CREATE INDEX statement

Creates an index on a specified table or materialized view.

  • Creating an index on a table
    ON [ owner.]table-name
        ( column-name [ ASC | DESC ], ...
          | function-name ( argument, [ ... ] ) AS column-name )
    [ { IN | ON } dbspace-name ]
  • Creating an index on a materialized view
      ON [ owner.]materialized-view-name
        ( column-name [ ASC | DESC ], ...)
    [ { IN | ON } dbspace-name ]
  • VIRTUAL clause

    The VIRTUAL keyword is primarily for use by the Index Consultant. A virtual index mimics the properties of a real physical index during the evaluation of execution plans by the Index Consultant and when the PLAN function is used. You can use virtual indexes together with the PLAN function to explore the performance impact of an index, without the often time-consuming and resource-consuming effects of creating a real index.

    Virtual indexes are not visible to other connections, and are dropped when the connection is closed. Virtual indexes are not used when evaluating plans for the actual execution of queries, and so do not interfere with performance.

    Virtual indexes have a limit of four columns.

    Creating virtual indexes (CREATE VIRTUAL INDEX statement) is not supported inside a BEGIN PARALLEL WORK statement.

  • UNIQUE clause

    The UNIQUE attribute ensures that there will not be two rows in the table or materialized view with identical values in all the columns in the index. If you specify UNIQUE, but do not specify WITH NULLS NOT DISTINCT, each index key must be unique or contain a NULL in at least one column. For example, two entries ('a', NULL) and ('a', NULL) are each considered unique.

    If you specify UNIQUE...WITH NULLS NOT DISTINCT, then the index key must be unique regardless of the NULL values. For example, two entries ('a', NULL) and ('a', NULL) are considered equal, not unique.

    There is a difference between a unique constraint and a unique index. Columns of a unique index are allowed to be NULL, while columns in a unique constraint are not. A foreign key can reference either a primary key or a unique constraint, but not a unique index, because it can include multiple instances of NULL.

    It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for primary keys or for columns in unique constraints. Approximate numeric data types are subject to rounding errors after arithmetic operations.

    Spatial columns cannot be included in a unique index.

  • CLUSTERED clause

    The CLUSTERED attribute causes rows to be stored in an approximate key order corresponding to the index. While the database server makes an attempt to preserve key order, total clustering is not guaranteed.

    If a clustered index exists, the LOAD TABLE statement inserts rows in the order of the index key, and the INSERT statement attempts to put new rows on the same page as the one containing adjacent rows, as defined by the key order.

  • IF NOT EXISTS clause

    When the IF NOT EXISTS attribute is specified and the named index already exists, no changes are made and an error is not returned.

  • ASC | DESC clause

    Columns are sorted in ascending (increasing) order unless descending (DESC) is explicitly specified. An index is used for both an ascending and a descending ORDER BY, whether the index was ascending or descending. However, if an ORDER BY is performed with mixed ascending and descending attributes, an index is used only if the index was created with the same ascending and descending attributes.

  • function-name

    The function-name clause creates an index on a function. This clause cannot be used on declared temporary tables or materialized views.

    This form of the CREATE INDEX statement is a convenience method that carries out the following operations:

    1. Adds a computed column named column-name to the table. The column is defined with a COMPUTE clause that is the specified function, along with any specified arguments. See the COMPUTE clause of the CREATE TABLE statement for restrictions on the type of function that can be specified. The data type of the column is based on the result type of the function.

    2. Populates the computed column for the existing rows in the table.

    3. Creates an index on the column.

      If you drop this index, its associated computed column is not dropped.

    Creating an index on a function (CREATE INDEX... ON function-name statement) is not supported inside a BEGIN PARALLEL WORK statement.

  • IN | ON clause

    By default, the index is placed in the same database file as its table or materialized view. You can place the index in a separate database file by specifying a dbspace name in which to put the index. This feature is useful mainly for large databases to circumvent file size limitations, or for performance improvements that might be achieved by using multiple disk devices.

    If the new index can share the physical index with an existing logical index, the IN clause is ignored.


    This clause can only be specified if you are declaring the index to be UNIQUE and allows you to specify that NULLs in index keys are not unique. For more information, see the UNIQUE clause.


    When you specify FOR OLAP WORKLOAD, the database server performs certain optimizations and gathers statistics on the key to help improve performance for OLAP workloads. Performance improvements are most noticeable when the optimization_workload is set to OLAP.


Indexes can improve database performance. The database server uses physical and logical indexes. A physical index is the actual indexing structure as it is stored on disk. A logical index is a reference to a physical index. If you create an index that is identical in its physical attributes to an existing index, the database server creates a logical index that shares the existing physical index. In general, indexes created by users are considered logical indexes. The database server creates physical indexes as required to implement logical indexes, and can share the same physical index among several logical indexes.

The CREATE INDEX statement creates a sorted index on the specified columns of the named table or materialized view. Indexes are automatically used to improve the performance of queries issued to the database, and to sort queries with an ORDER BY clause. Once an index is created, it is never referenced in a SQL statement again except to validate it (VALIDATE INDEX), alter it (ALTER INDEX), delete it (DROP INDEX), or in a hint to the optimizer.

  • Index ownership

    There is no way of specifying the index owner in the CREATE INDEX statement. Indexes are always owned by the owner of the table or materialized view.

  • Indexes on tables

    You can create indexes on base tables, and on both local and global temporary tables.

    When the CREATE INDEX statement is inside a BEGIN PARALLEL WORK statement, you can only create indexes on base tables.

  • Indexes on views

    You can create indexes on materialized views, but not on regular views.

  • Index name space

    The name of each index must be unique for a given table or materialized view.

  • Exclusive use

    CREATE INDEX is prevented whenever the statement affects a table or materialized view currently being used by another connection.

  • Automatically created indexes

    The database server automatically creates indexes for primary key, foreign key, and unique constraints. These automatically created indexes are held in the same database file as the table.

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

When the CREATE INDEX statement is inside the BEGIN PARALLEL WORK statement, the granularity is at the table level. CREATE INDEX statements that create indexes on the same table are executed sequentially, even if they appear inside a BEGIN PARALLEL WORK statement.


To create an index on a table, you must be the owner of the table, or have one of the following privileges:

  • REFERENCES privilege on the table
  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege

To create an index on a materialized view, you must be the owner of the materialized view, or have one of the following privileges:

  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege
Side effects

Automatic commit in most cases. If the auto_commit_on_create_local_temp_index option is set to Off, there is no commit before creating an index on a local temporary table. Creating an index on a function (an implicit computed column) causes a checkpoint.

Column statistics are updated (or created if they do not exist).

The CREATE INDEX statement only applies an exclusive lock to the table for a limited time at the beginning and at the end of its execution. In the middle of the CREATE INDEX statement's execution, a shared lock is applied to the table. Statements that require exclusive access to the table for the entirety of their execution cannot be executed concurrently with the CREATE INDEX statement even though its exclusive lock is only temporary. However, statements that only require shared access can be executed on the table, but only in the middle of the CREATE INDEX statement's execution.

  • ANSI/ISO SQL Standard

    Not in the standard.


Create a two-column index on the Employees table.

CREATE INDEX employee_name_index
ON GROUPO.Employees
( Surname, GivenName );

Create an index on the SalesOrderItems table for the ProductID column.

CREATE INDEX item_prod
ON GROUPO.SalesOrderItems
( ProductID );

Use the SORTKEY function to create an index on the Description column of the Products table, sorted according to a Russian collation. As a side effect, the statement adds a computed column desc_ru to the table. For this example to succeed, you must also have SELECT privilege on the Products table.

CREATE INDEX ix_desc_ru
ON GROUPO.Products (
 SORTKEY( Description, 'rusdict' )
 AS desc_ru );