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

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Statement Reference

UltraLite COMMIT statement Next Page

UltraLite CREATE INDEX statement

Creates an index on a specified table. See Optimizing UltraLite query performance .


CREATE [ UNIQUE ] INDEX [ index-name ]
ON table-name ( ordered-column-list )

ordered-column-list :
[ ( column-name [ ASC | DESC ], ..., ] ) ]


UNIQUE    The UNIQUE parameter ensures that there are not two rows in the table with identical values in all the columns in the index. Each index key must be unique or contain a NULL in at least one column.

There is a difference between a unique constraint on a table and a unique index. Columns in 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 column with a unique constraint, but not a unique index, because it can include multiple instances of NULL.

ordered-column-list    An ordered list of columns. The ordered list can be sorted in ascending or descending order.

WITH MAX HASH SIZE    Sets the default index hash size in bytes. If you do not set this value, a default size of 4 bytes is used. See UltraLite max_hash_size property.


Indexes can improve query performance by providing quick ways for UltraLite to look up specific rows. Conversely, because they have to be maintained, indexes may slow down INSERT, DELETE, and UPDATE statements, as well as synchronization.

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 remove it with DROP INDEX.

Indexes use space in the database. Also, the additional work required to maintain indexes can affect the performance of data modification operations. For these reasons, you should avoid creating indexes that do not improve query performance.

UltraLite does not process requests or queries referencing the index while the CREATE INDEX statement is being processed. Furthermore, you cannot execute CREATE INDEX when the database includes active queries or uncommitted transactions.

UltraLite can also use query access plans to optimize queries. See Query access plans in UltraLite.

For UltraLite.NET users: You cannot execute this statement, unless you also call the Dispose method for all data objects (for example, ULDataReader). See Dispose method.

UltraLite automatically creates indexes for primary keys and for unique constraints.

Statements are not released if schema changes are initiated at the same time. See Schema changes with DDL statements.

Side effects
See also

The following example creates a two-column index on the employee table.

CREATE INDEX employee_name_index
ON employee
( emp_lname, emp_fname )

The following example creates an index on the sales_order_items table for the prod_id column.

CREATE INDEX item_prod
ON sales_order_items
( prod_id )