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 ], ... )

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.

UltraLite can provide developers with execution plans to optimize queries. See Execution 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 ULBulkCopy.Dispose method [UltraLite.NET].

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

• Automatic commit.

• UltraLite performance tips
• DROP INDEX statement [UltraLite]
• UltraLite max_hash_size creation parameter
• UltraLite indexes

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 );