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

SQL Anywhere 17 » UltraLite - Database Management and Developer Guide » UltraLite SQL reference » UltraLite SQL statements

CREATE INDEX statement [UltraLite]

Creates an index on a specified table.

Syntax
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ]  [ index-name ]
ON table-name ( ordered-column-list ) 
[ WITH MAX HASH SIZE integer ]
ordered-column-list :
( column-name [ ASC | DESC ], ... )
Parameters
  • UNIQUE

    The UNIQUE attribute 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 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.

    If the columns in a unique constraint are changed during an update, and a foreign key references that unique constraint, any rows no longer referencing rows in the unique constraint are deleted from the remote.

  • 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.

  • ordered-column-list

    An ordered list of columns. Column values in the index can be sorted in ascending or descending order.

  • WITH MAX HASH SIZE

    Sets the hash size (in bytes) for this index. This value overrides the default MaxHashSize database property in effect for the database.

Remarks

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

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 synchronization and INSERT, DELETE, and UPDATE statements.

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.

Use execution plans to optimize queries.

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

Statements are not released if database schema changes are initiated at the same time.

Side effects
  • Automatic commit.

Example

The following statement creates a two-column index on the Employees table.

CREATE INDEX employee_name_index
ON Employees ( Surname, GivenName )

The following statement creates an index on the SalesOrderItems table for the ProductID column.

CREATE INDEX item_prod
ON SalesOrderItems ( ProductID )

The following scenario illustrates the effects of MAX HASH SIZE on an UltraLite Java edition database, given an Employees table that contains an Initials column that is VARCHAR( 3 ) and an EmployeeID column that is TINY.

The following statement completely hashes all values when only ASCII7 characters are used:

CREATE INDEX ascii_a ON Employees( Initials ) WITH MAX HASH SIZE 3

The following statement completely hashes all values no matter what characters they contain:

CREATE INDEX unicode_a ON Employees( Initials ) WITH MAX HASH SIZE 9

The following statement only hashes the Initials values even when only ASCII characters are used because the first 9 bytes for Initials are reserved:

CREATE INDEX compound_1 ON Employees( Initials, EmployeeID ) WITH MAX HASH SIZE 9

The following statement completely hashes both Initials and EmployeeID values:

CREATE INDEX compound_2 ON Employees( Initials, EmployeeID ) WITH MAX HASH SIZE 10